JuniorTechnicalSQL

What is a CTE and how does it differ from a subquery?

Try answering first

Model answer

A CTE (common table expression) is a named temporary result defined with WITH and used in the same statement. A subquery is an unnamed query nested inside another one.

WITH recent AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  WHERE created_at >= CURRENT_DATE - 30
  GROUP BY customer_id
)
SELECT c.name, r.spent
FROM recent r
JOIN customers c ON c.id = r.customer_id
WHERE r.spent > 1000;

The same logic as a derived-table subquery returns the same rows. The CTE wins on readability: you name each step and read the query top to bottom. A CTE can also be referenced several times, and only a CTE can be recursive.

On performance, do not assume a CTE is a cached temp table. PostgreSQL 12+ inlines a non-recursive CTE that is used once, unless you write MATERIALIZED. Other engines may or may not materialize it, so check the plan.

Use a CTE to structure a complex query, and a plain subquery for a small one-off condition.

Key points

A CTE is a named result scoped to one statement
CTEs improve readability and can be reused or recursive
Materialization behavior depends on the engine and version

Likely follow-ups

When would you force MATERIALIZED in PostgreSQL?

Common mistakes

Believing a CTE always runs once and is cached
Believing a CTE is always faster than a subquery