CTEs (WITH clause)

Concepts covered: Common Table Expressions

A common table expression, written with WITH, gives a result set a name at the top of a statement so every clause below can read it by that name. The database treats the name as a table that lives for one statement: defined before the main query starts, readable by it, gone when it finishes. A 300-line report that computes 6 intermediate results becomes 6 named steps and one short final SELECT, and any step can be checked on its own by selecting from its name.

One CTE, one filter

The shape is WITH name AS (query) followed by the main query. Everything inside the parentheses is an ordinary SELECT, and everything after the closing parenthesis reads name as if it were a table in the schema. The animation below runs 2 filters in sequence over a 5-row users table: the CTE active_set keeps the rows where active = 1, and the outer query keeps the rows whose id is above 100.

WITH active_set AS (
  SELECT id, name FROM users WHERE active = 1
)
SELECT id, name
FROM active_set
WHERE id > 100

Watch the row counts. The CTE scans 5 users and keeps 3: Ava (98), Cleo (101) and Eli (103). The outer WHERE id > 100 never sees the original table; it's applied to those 3 rows and keeps Cleo and Eli. Ben (99) and Dev (102) were already gone before the second filter ran, and that sequence, inner query complete before the outer query starts, is the whole mechanism.

Chaining steps

Several CTEs can share one WITH with commas between them, and each can read any CTE declared above it. That turns a transformation with stages into a query with named stages. Below, active_users is the first stage, user_totals sums each user's orders (a GROUP BY, which has its own lesson), and the final SELECT joins the 2 on the user id. Run it, then replace the final SELECT with SELECT * FROM user_totals to inspect one stage alone.

WITH active_users AS (
  SELECT id, name
  FROM users
  WHERE active = 1
),
user_totals AS (
  SELECT user_id, SUM(total) AS spent, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
)
SELECT u.id, u.name, t.spent, t.order_count
FROM active_users u
JOIN user_totals t ON t.user_id = u.id
ORDER BY t.spent DESC

The result holds 3 rows because only 3 users are active, and Ava leads with 130 across 2 orders. Ben spent 75 but is inactive, so user_totals computed his row and the join dropped it. That's the debugging pattern CTEs make possible: when a number looks wrong, select from the stage where it was born. The fault is in that stage or one it reads from, so check each upstream stage by selecting from it.

Scope and reuse

A CTE exists only inside the statement that declares it; the next statement in the same session can't see it. Declaration order is binding: a CTE may read the ones above it and never the ones below. One name may be referenced as many times as the query needs, which is where CTEs beat a copied subquery. The query below reads active_users twice to compute 2 counts (the subqueries in its SELECT list are the subject of section 2), and a change to the definition of active reaches both.

WITH active_users AS (
  SELECT id FROM users WHERE active = 1
)
SELECT
  (SELECT COUNT(*) FROM active_users) AS active_count,
  (SELECT COUNT(*) FROM orders
   WHERE user_id IN (SELECT id FROM active_users)) AS active_orders

Whether the engine computes a twice-referenced CTE once or inlines it at each reference depends on the product. PostgreSQL 12 and later inline a CTE referenced once and materialize one referenced twice; MATERIALIZED and NOT MATERIALIZED override the default. BigQuery and Snowflake make their own choice per query. The rows are the same either way, so the writing rule stays: name a step once, read it wherever it's needed.

Names carry the meaning

A CTE's name is the only documentation most readers will get, so it names what one row of it represents. Names like active_users and monthly_revenue say what a row is, and so does late_shipments. With cte1 or temp the reader has to go back into the parentheses to find out, and the same goes for data.

Names that teach

• active_users: 1 row per user whose account is active
• monthly_revenue: 1 row per month with its revenue
• late_shipments: 1 row per shipment past its promised date

Names that hide

• cte1: the reader opens the parentheses to find out
• temp: says the data is temporary, which every CTE is
• data: names nothing the query doesn't already have

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.