CTEs vs subqueries: when to reach for which

Use a CTE when a derived result is reused, when you're building a named pipeline of steps, or when you need recursion; use a subquery when it's a small, one-off filter inline where it's needed. A common interview misconception is that CTEs are "slower" than subqueries. On modern engines they usually plan to the same thing — so the real question isn't speed, it's which one a human can read.

The short answer: reach for a CTE for reuse, pipelines, and recursion; reach for a subquery for a single small inline filter.

Reach for a CTE when

A common table expression (CTE) earns its keep when structure helps the reader:

• The same derived set is used more than once in the query.
• You're building a pipeline of steps and want each one named.
• You need recursion — hierarchies, graphs, or running sequences.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

That reads top to bottom like a sentence: build ranked, then filter it. The intent is on the surface, which is exactly what you want when someone else (or future you) has to change it.

Reach for a subquery when

A subquery is the lighter choice when naming a step would be ceremony:

• It's used once and is small.
• It's a simple scalar or an IN (...) filter, inline where it's read.

SELECT name, salary
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE region = 'EU');

Lifting that into a CTE wouldn't make it clearer — it would just add a name you read once and forget.

The performance myth

Optimize the query for the next person who reads it. Most of the time, that person is you.

Don't choose by superstition about speed. On modern query planners, a non-recursive CTE and the equivalent subquery typically produce nearly identical execution plans. Choose by what makes the intent obvious, and reach for EXPLAIN only when a query is actually slow — not as a tiebreaker between two readable options.

How this shows up on CodeOak

CodeOak's SQL query track grades the table your query returns — by deterministic comparison against the expected output, not an AI opinion of your style. So both a clean CTE and an equivalent subquery pass if the result is right. What separates strong candidates is making the next reader's job easy, and that judgment is part of what the assessment surfaces under code quality.

The CTE example above leans on a window function — if ROW_NUMBER() OVER (...) is new, read window functions explained. The Python sibling of this "write for the reader" idea is knowing when a comprehension has gone too far.

FAQ

Are CTEs slower than subqueries in SQL? Usually not. On modern engines, a non-recursive CTE and the equivalent subquery typically compile to nearly the same execution plan. Choose based on readability, and only profile with EXPLAIN when a specific query is actually slow.

When should I use a CTE instead of a subquery? Use a CTE when the same derived set is referenced more than once, when you want to name the steps of a multi-stage query, or when you need recursion. Use a subquery for a single, small, inline filter.

Can a CTE do recursion that a subquery can't? Yes. Recursive CTEs (WITH RECURSIVE) handle hierarchies, graphs, and generated sequences — patterns a plain subquery can't express. That's one of the clearest cases for reaching for a CTE.