What is a CTE?

How it works

WITH name AS (SELECT ...) defines a result set the rest of the statement can read by name. Several CTEs can be chained, each reading the ones before it, so a query reads top to bottom as a pipeline: clean, rank, filter, aggregate. This is also the house style in dbt, where models are written as a series of import and logic CTEs.

A CTE exists only for one statement. Compared with the alternatives:

• Subqueries do the same job inline, but nest inward and cannot be reused by name.
• Temporary tables persist for the session, can be indexed and are computed once, which helps when a large intermediate result is reused across statements.
• Views are stored permanently for everyone.

Most modern engines inline CTEs into the main query and optimize the whole thing together. PostgreSQL 12+ inlines a CTE referenced once and materializes one referenced several times unless told otherwise (MATERIALIZED / NOT MATERIALIZED). Spark, Snowflake and BigQuery may recompute a CTE each time it is referenced.

A recursive CTE (WITH RECURSIVE) has an anchor query and a recursive part joined with UNION ALL; it repeats until no new rows appear. It is the standard SQL tool for org charts, bills of materials, category trees and date series.

Example

Two chained CTEs find customers whose latest order was cancelled, and a recursive CTE walks a reporting line (PostgreSQL 16).

CREATE TABLE orders (order_id int, customer_id text, status text, order_date date);
INSERT INTO orders VALUES (1, 'C1', 'shipped', '2026-10-01'), (2, 'C1', 'cancelled', '2026-10-09'),
                          (3, 'C2', 'cancelled', '2026-10-02'), (4, 'C2', 'shipped', '2026-10-07');

-- Readable steps: latest order per customer, then keep those that were cancelled.
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
),
latest AS (
  SELECT customer_id, status, order_date FROM ranked WHERE rn = 1
)
SELECT * FROM latest WHERE status = 'cancelled';

-- Recursive CTE: walk a reporting hierarchy from the top.
CREATE TABLE employees (id int, name text, manager_id int);
INSERT INTO employees VALUES (1, 'Asha', NULL), (2, 'Ravi', 1), (3, 'Meera', 1), (4, 'Kabir', 2);

WITH RECURSIVE chain AS (
  SELECT id, name, 1 AS level, name::text AS path FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.level + 1, c.path || ' > ' || e.name
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT level, path FROM chain ORDER BY path;

 customer_id |  status   | order_date
-------------+-----------+------------
 C1          | cancelled | 2026-10-09

 level |        path
-------+---------------------
     1 | Asha
     2 | Asha > Meera
     2 | Asha > Ravi
     3 | Asha > Ravi > Kabir

Common mistakes

• Assuming a CTE is computed once and cached; many engines rerun it at every reference.
• Writing a recursive CTE without a guaranteed stop, such as on data with cycles, which loops until a limit is hit.
• Using UNION instead of UNION ALL in a recursive CTE without meaning to deduplicate.
• Nesting a dozen CTEs in one statement when a few intermediate models or tables would be easier to test.