CTEs: The WITH Clause

A Common Table Expression (CTE) is a named, temporary result set defined at the top of a query using the WITH keyword. Once defined, you reference it like a table anywhere in the query that follows. CTEs do not store data permanently; they exist only for the duration of the query.

The primary reason to use a CTE is readability. Complex SQL queries often involve multi-step logic: first compute this, then filter that, then join the result to something else. Without CTEs, all of that logic lives as nested subqueries, which are hard to read, hard to test, and easy to break. With CTEs, each step gets a name and lives on its own line.

The basic form

WITH high_earners AS (
  SELECT id, name, salary, department_id
  FROM employees
  WHERE salary > 100000
)
SELECT d.name AS department, COUNT(*) AS count
FROM high_earners h
JOIN departments d ON h.department_id = d.id
GROUP BY d.name;

The CTE high_earners is defined once. The main query treats it exactly like a table. The query reads from top to bottom in the order you would think through the problem: first identify who the high earners are, then ask which departments they are in.

Multiple CTEs

You can define as many CTEs as you need by separating them with commas. Each CTE can reference the ones defined before it:

WITH
dept_stats AS (
  SELECT department_id,
         AVG(salary)  AS avg_salary,
         COUNT(*)     AS headcount
  FROM employees
  GROUP BY department_id
),
large_depts AS (
  SELECT department_id
  FROM dept_stats
  WHERE headcount >= 20
)
SELECT d.name, ds.avg_salary, ds.headcount
FROM departments d
JOIN dept_stats ds   ON ds.department_id = d.id
JOIN large_depts ld  ON ld.department_id = d.id
ORDER BY ds.avg_salary DESC;

Notice that large_depts references dept_stats. This chaining is one of the things that makes CTEs so expressive: each step builds on the last, and the final SELECT assembles everything.

CTEs vs subqueries: when to choose each

CTEs and subqueries are often interchangeable in terms of what they can express. The difference is primarily about communication: code is read far more often than it is written.

Situation	Prefer
One-time, simple filter	Subquery in WHERE
Derived table used once	Subquery in FROM
Logic used in more than one place	CTE
Multi-step logic that benefits from naming	CTE
Want to test each step in isolation	CTE

A good test: if you could not easily explain what a subquery does without reading its full contents, it probably deserves a name as a CTE.

Debugging CTEs

One practical advantage of CTEs is that you can test each step by running just that piece. If you want to verify what dept_stats returns, copy its query and run it standalone. This is much harder with nested subqueries, which require you to extract and untangle code.

Performance and materialization

As of PostgreSQL 12, non-recursive CTEs are inlined by default. The query planner treats them as if they were subqueries, which means it can push conditions through them and optimize freely. Older PostgreSQL versions treated each CTE as an optimization barrier, evaluating it in full before the outer query could filter the result.

If you need to force a CTE to be evaluated exactly once (materialized), PostgreSQL supports an explicit hint:

WITH expensive_calculation AS MATERIALIZED (
  SELECT ...
)

This is an optimization concern for specific situations. For most queries, write for clarity first and let the planner do its job.

The path to recursive CTEs

The WITH keyword enables one more feature beyond readability: recursive queries. A recursive CTE can call itself, which is the SQL mechanism for traversing hierarchies and graphs. That is covered in its own guide in the Advanced section. Understanding regular CTEs first makes the recursive form much easier to follow.