SQL-Engineering-Handbook

Subquery and CTE Optimization

Module 16 · Query Optimization Home · Engineering Guide · Cheatsheet · Rewrite Cookbook

Lesson 05 of 12 · ← Joins · Next: Anti-Patterns → · SQL Lab · Lab Benchmark

Introduction

IN, EXISTS, correlated subqueries, and CTEs can all express the same logical question — but they don’t always compile to the same physical plan. This lesson covers when they diverge and how to choose correctly.

Learning Objectives

Why This Exists

These four constructs are functionally interchangeable in many cases, which tempts SQL authors into using whichever “feels natural” without checking whether the optimizer treats them identically. It often doesn’t.

Business Motivation

“Which departments have at least one employee hired in the last 30 days?” can be written four different ways, and at production scale the performance of these can differ by an order of magnitude depending on the engine, data distribution, and whether NULL values are present in the subquery’s column.

EXISTS vs. IN

flowchart LR
    A[IN: materialize full subquery set] --> C[Check membership]
    B[EXISTS: stop at first match] --> C
    style B fill:#dcfce7,stroke:#15803d

-- IN
SELECT dept_name
FROM departments d
WHERE d.dept_id IN (
    SELECT dept_id FROM employes WHERE hire_date > CURRENT_DATE - INTERVAL 30 DAY
);

-- EXISTS
SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employes e
    WHERE e.dept_id = d.dept_id
      AND e.hire_date > CURRENT_DATE - INTERVAL 30 DAY
);

Correlated Subqueries

A correlated subquery references a column from the outer query, meaning it conceptually re-runs once per outer row:

-- Correlated: "each employee's hire_date rank within their own department"
SELECT e.emp_name,
       (SELECT COUNT(*) FROM employes e2
        WHERE e2.dept_id = e.dept_id AND e2.hire_date <= e.hire_date) AS hire_rank
FROM employes e;

Naively, this looks like it must literally execute once per outer row — in practice, good optimizers can rewrite this into a single join-based plan. But not all engines do this well for all shapes, and this exact “rank within group” problem has a purpose-built alternative that avoids per-row correlated re-execution: a window function. It’s not a guarantee of speed on every engine and dataset, but it removes the specific O(n) re-execution pattern that makes the correlated form risky at scale.

-- Window function rewrite — same result, no per-row correlated re-execution
SELECT e.emp_name,
       RANK() OVER (PARTITION BY e.dept_id ORDER BY e.hire_date) AS hire_rank
FROM employes e;

Rule of thumb: if a correlated subquery is computing a per-group rank, running total, or comparison to peers, a window function (Module 07) is almost always both clearer and faster than the correlated form.

CTEs: Inlined vs. Materialized

A CTE is a name for a subquery — it does not automatically mean “computed once and cached,” and it does not automatically mean “always inlined,” either. Behavior differs by engine:

WITH fraud_dept AS (
    SELECT dept_id FROM departments WHERE dept_name = 'Fraud Review'
)
SELECT e.emp_name
FROM employes e
JOIN fraud_dept fd ON e.dept_id = fd.dept_id;

This is optimization-equivalent to writing the subquery inline, on modern Postgres/SQL Server/MySQL — the CTE here is purely a readability choice, not a performance one.

Engineering Notes

MySQL / PostgreSQL / SQL Server Notes

Common Mistakes

Anti-patterns

Interview Questions

Edge Cases

Troubleshooting Guidance

Scalability Considerations

Correlated subqueries that aren’t rewritten by the optimizer scale per outer row — on a small outer set this is invisible, but on a large outer table this is the difference between a query that finishes in milliseconds and one that never finishes. Always validate correlated subquery performance against production-scale outer table sizes, not just sample data.

Additional Dialect Notes

Summary

IN, EXISTS, correlated subqueries, and CTEs are tools with different default behaviors across engines — the fix for “which one is faster” is always to check EXPLAIN for your specific engine and data, not to trust a rule of thumb blindly. The one true rule of thumb that holds broadly: prefer NOT EXISTS over NOT IN for correctness, and prefer window functions over correlated subqueries for per-group ranking.

Practice Challenges

  1. Rewrite a NOT IN subquery from your own past work as NOT EXISTS and explain the correctness difference if the subquery column can be NULL.
  2. Take the hire-rank correlated subquery example and confirm via EXPLAIN that the window function version produces a cheaper plan on your engine.

Real Company Usage

Production Applications

These scenarios are illustrative case studies inspired by common production failure modes; specific figures are illustrative unless an external source is cited in PRODUCTION_INCIDENTS.md. See its disclaimer for the full explanation.

Performance Notes

Optimization Notes

Interview Insight

Subquery questions often present a correlated subquery and ask you to rewrite it. Interviewers look for: (1) recognizing the per-row execution cost, (2) proposing EXISTS/JOIN/window-function alternatives, (3) knowing the NOT IN NULL trap. For CTEs, mention that PostgreSQL 12+ changed default inlining behavior — this signals production awareness beyond textbook knowledge.

Further Experiments

  1. Write a correlated subquery counting per-department employees, confirm SubPlan loops=N in EXPLAIN ANALYZE, then rewrite with COUNT() OVER (PARTITION BY dept_id).
  2. Test NOT IN vs. NOT EXISTS with a nullable column in the subquery — verify that NOT IN silently drops rows when NULL is present.
  3. Complete the Subquery Rewrite Benchmark and compare elapsed times across rewrite stages.

Continue Learning

04_Subqueries · 06_CTEs · 07_Window_Functions

Further Reading

This site is open source. Improve this page.