Are CTEs faster than subqueries?

On modern databases they're usually the same speed because the optimizer treats them equivalently. PostgreSQL 12+, MySQL 8+, and SQL Server inline single-use CTEs into the main query. The notable exception is PostgreSQL 11 and earlier, where CTEs were always materialized and could be slower, so choose based on readability unless profiling says otherwise.

Can a CTE do something a subquery can't?

Yes — recursion. A recursive CTE (WITH RECURSIVE) can traverse hierarchical data like org charts, bill-of-materials, or category trees, which a plain subquery cannot express. CTEs can also be referenced multiple times within one query, whereas a subquery must be repeated each place it's needed.

What is the CTE optimization fence in PostgreSQL?

Before PostgreSQL 12, every CTE was materialized into a temporary result that the planner could not optimize across — it couldn't push WHERE filters into the CTE. This "optimization fence" sometimes made CTEs slower than subqueries. Postgres 12 changed the default to inline single-use CTEs; you can still force the old behavior with AS MATERIALIZED.

Should I always use CTEs instead of subqueries?

No. CTEs shine for complex, reused, or recursive logic, but a simple scalar filter or a correlated EXISTS reads more clearly as an inline subquery. Hoisting trivial logic into a WITH block adds ceremony without improving clarity. Match the construct to the complexity of the logic.

CTE vs Subquery: When to Use Which in SQL

sqlfmt▍

CTE vs Subquery: When to Use Which in SQL — sqlfmt

CTE vs subquery is mostly a readability decision, not a performance one. A common table expression (the WITH clause) names an intermediate result and lets you read a query top-to-bottom; a subquery nests that result inline where it's used. On modern databases they typically produce the same plan, so the right choice is whichever makes the query easier to understand.

There are real exceptions — recursion only works with CTEs, correlated subqueries have no clean CTE equivalent, and older PostgreSQL treated CTEs as an optimization fence. This guide covers when each wins and the one performance gotcha worth knowing.

What's the difference between a CTE and a subquery?

A subquery is a SELECT nested inside another query — in the FROM clause, WHERE clause, or SELECT list. A CTE is a named, temporary result set defined in a WITH clause at the top of the statement, then referenced by name below. They express the same logic; the difference is placement and naming.

Same logic, two shapes

-- Subquery (inline, nested)
SELECT *
FROM (
    SELECT customer_id, SUM(total) AS lifetime
    FROM orders
    GROUP BY customer_id
) t
WHERE t.lifetime > 1000;

-- CTE (named, top-down)
WITH customer_totals AS (
    SELECT customer_id, SUM(total) AS lifetime
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE lifetime > 1000;

Recursive CTE: only a CTE can do this

WITH RECURSIVE subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Force materialization on PostgreSQL 12+ when you want it

WITH heavy AS MATERIALIZED (
    SELECT customer_id, SUM(total) AS lifetime
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM heavy WHERE lifetime > 1000;

CTE vs Subquery: When to Use Which in SQL

Quick answer

FAQs

Are CTEs faster than subqueries?

Can a CTE do something a subquery can't?

What is the CTE optimization fence in PostgreSQL?

Should I always use CTEs instead of subqueries?

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

What's the difference between a CTE and a subquery?

When to use a CTE

When a subquery is the better choice

Do CTEs perform worse than subqueries?

Readability: the real deciding factor

Checklist: CTE or subquery?

Wrapping up

Quick answer

FAQs

Are CTEs faster than subqueries?

Can a CTE do something a subquery can't?

What is the CTE optimization fence in PostgreSQL?

Should I always use CTEs instead of subqueries?

Keep reading

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

What's the difference between a CTE and a subquery?

When to use a CTE

When a subquery is the better choice

Do CTEs perform worse than subqueries?

Readability: the real deciding factor

Checklist: CTE or subquery?

Wrapping up