When should you use a CTE instead of a subquery?

Nested queries age badly: the query that made sense inside-out in March is unreadable by June. Here is when to name a step and when an inline lookup is fine. The print-ready A4 PDF is at the bottom.

The CTE

• WITH step AS (…): name a step.
• SELECT … FROM step: the query reads top-down.
• WITH a AS (), b AS (): chain steps, one per transformation.

The subquery

• (SELECT …) AS t: inline, used once.
• IN (SELECT id …): filter by a lookup.
• EXISTS (SELECT 1 …): a presence check.

Choosing

• Used twice? CTE.
• One-line lookup? Subquery.
• Debugging? CTE: run each step on its own.
• Three levels deep? CTE, always.

The top-N interview pattern

WITH ranked AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC) AS rn
  FROM orders)
SELECT * FROM ranked WHERE rn = 1;

You cannot filter on ROW_NUMBER in the same SELECT that defines it. The CTE is what makes WHERE rn = 1 legal, and it wins on readability over the self-join answer.

Performance

• CTE vs subquery: engines inline both, plans usually match.
• AS MATERIALIZED: force an expensive CTE to compute once.
• Correlated subquery: reruns once per outer row, the actual trap.
• WITH RECURSIVE exists for hierarchies; you will rarely need it.

The trap: NOT IN meets NULL

One NULL in the subquery and the whole result is empty, silently. And the next person to read your query is you, in six months: name the step.