Common Table Expressions (CTEs)
Name each step of a query with WITH, reuse it, and generate series with WITH RECURSIVE
What Is a CTE in SQL?
A common table expression (CTE) is a named, temporary result set that you define with WITH at the start of a statement and then query like a table. It exists only while that one statement runs.
FROM and again in a WHERE subquery, without writing its query twice.WITH RECURSIVE, a CTE can refer to itself. That is how you generate number and date series or walk a hierarchy such as an org chart.Knowledge Canvas
How CTEs Work
Named temporary result sets
WITH name AS (query)defines a named result set- Several CTEs: one
WITH, a comma between them - Each CTE can read the CTEs defined before it
- A CTE lasts for one statement only
How to Read a CTE Chain
Each step can use the ones above it
WITH step1 AS (...) reads base tablesstep2 AS (...) can read step1step3 AS (...) can read step1 and step2Scope and Lifetime at a Glance
Subquery, CTE, temp table and view
Recursive CTE in Three Parts
Anchor, recursive member, stop condition
- Anchor: runs once and gives the starting rows
- Recursive member: runs on the previous step's rows
- Stops when a step returns no rows
UNION ALLstacks the rows of every step
WITH RECURSIVE numbers AS (
SELECT 1 AS n -- anchor
UNION ALL
SELECT n + 1 -- recursive member
FROM numbers
WHERE n < 5 -- stop condition
)
SELECT n FROM numbers; -- 1 to 5CTE Gotchas
Where CTE queries go wrong
- The next statement can't see the CTE
ORDER BYinside a CTE doesn't sort the final result- Recursive stop condition:
n < 5gives 1–5,n <= 5gives 1–6 - Default recursion limits: SQL Server 100, MySQL 1,000; PostgreSQL and SQLite none
CTE Patterns
Common real-world structures
- Compute a value once (an average) and compare every row to it
- Aggregate each child table in its own CTE before joining
- Recursive calendar + LEFT JOIN to show empty months as 0
- Number rows with ROW_NUMBER in a CTE, then filter in the outer query
Best Practices
Writing clean CTEs
- Name CTEs after what they contain:
customer_totals, notstep2 - Give every computed column an alias
- Don't reuse a table's name for a CTE
- Test a step by ending the query with
SELECT * FROM step_name
Syntax
WITHStarts the list of CTEs. Write it once, before the first CTE.name AS ( query )Defines one CTE. Pick a name that is not also a table name, and give every computed column an alias (SUM(total) AS total_spent) or name the columns in the optional column list.,Separates one CTE from the next. No comma after the last CTE.(col1, col2)Optional column list. Names the CTE's columns in order, replacing the names from the query.RECURSIVENeeded when a CTE refers to itself in PostgreSQL and MySQL. Optional in SQLite; SQL Server and Oracle leave it out.Anchor memberThe first SELECT of a recursive CTE. Runs once and produces the starting rows.UNION ALLAdds the rows of each recursive step to the result.Recursive memberThe SELECT that reads the CTE itself. Runs on the previous step's rows; its WHERE is the stop condition.Sample Data
| id | customer_id | order_date | status | total |
|---|---|---|---|---|
| 5001 | C1001 | 2024-01-15 | delivered | 59.98 |
| 5002 | C1002 | 2024-02-08 | delivered | 449 |
| 5003 | C1001 | 2024-02-20 | shipped | 12.5 |
| 5004 | C1003 | 2024-03-05 | delivered | 89.99 |
| 5005 | C1006 | 2024-03-15 | pending | 245 |
| 5006 | C1005 | 2024-03-22 | delivered | 159 |
| 5007 | C1004 | 2024-04-02 | shipped | 45.5 |
| 5008 | C1009 | 2024-04-12 | cancelled | 79 |
| 5009 | C1012 | 2024-04-22 | delivered | 425 |
| 5010 | C1007 | 2024-05-05 | pending | 18.99 |
| 5011 | C1008 | 2024-05-18 | delivered | 134 |
| 5012 | C1010 | 2024-06-08 | cancelled | 65.5 |
| id | name |
|---|---|
| C1001 | Aarav Sharma |
| C1002 | Sara Chen |
| C1003 | James Wilson |
| C1004 | Maria Garcia |
| C1005 | Yuki Tanaka |
| C1006 | Priya Patel |
| C1007 | Alex Johnson |
| C1008 | Chen Wei |
| C1009 | Emma Brown |
| C1010 | Omar Hassan |
| C1011 | Lena Muller |
| C1012 | Ravi Kumar |
| id | name |
|---|---|
| P101 | Wireless Mouse |
| P102 | Office Desk |
| P103 | LED Desk Lamp |
| P104 | USB-C Cable |
| P105 | Bluetooth Speaker |
| P106 | Office Chair |
| P107 | Coffee Maker |
| P108 | Notebook Set |
| P109 | Wireless Headphones |
| P110 | Standing Desk |
| order_id | product_id | quantity |
|---|---|---|
| 5001 | P101 | 2 |
| 5001 | P104 | 1 |
| 5002 | P109 | 2 |
| 5002 | P102 | 1 |
| 5003 | P108 | 1 |
| 5004 | P107 | 1 |
| 5005 | P106 | 1 |
| 5005 | P102 | 1 |
| 5006 | P109 | 1 |
| 5007 | P103 | 1 |
| 5008 | P105 | 1 |
| 5009 | P110 | 1 |
| 5010 | P101 | 1 |
| 5011 | P105 | 1 |
| 5011 | P103 | 1 |
| 5012 | P107 | 1 |
| product_id | rating |
|---|---|
| P101 | 5 |
| P101 | 4 |
| P101 | 3 |
| P102 | 5 |
| P102 | 4 |
| P103 | 4 |
| P104 | 5 |
| P105 | 5 |
| P105 | 4 |
| P107 | 3 |
| P108 | 5 |
| P109 | 5 |
Reuse One CTE Instead of Repeating a Subquery
Find the customers whose orders add up to more than the average customer's.
customer_totals adds up each customer's orders, whatever their status: 11 of the 12 customers have orders (Lena Muller has none), and their totals average 162.13. The final query uses that one CTE twice, in FROM to list the customers and in the WHERE subquery to compute the average. Three customers are above it: Sara Chen (449), Ravi Kumar (425) and Priya Patel (245).Without a CTE you would write the same
GROUP BY query twice, once as a derived table in FROM and again inside the AVG subquery. That version returns the same 3 rows, but every later change has to be made in both places. You could also move the average into a second CTE, avg_spend AS (SELECT AVG(total_spent) AS avg_total FROM customer_totals), because each CTE can read the ones listed before it.| name | total_spent |
|---|---|
| Sara Chen | 449 |
| Ravi Kumar | 425 |
| Priya Patel | 245 |
Multiple CTEs: Aggregate Each Table Before Joining
For each product that has both sales and reviews, show the units sold, the average rating and the number of reviews.
units totals the quantity ordered per product, whatever the order's status (as in the first example), and ratings summarizes the reviews per product. Each CTE has exactly one row per product, so joining them to products can't duplicate anything: Wireless Mouse sold 3 units (2 in order 5001, 1 in order 5010) and has 3 reviews averaging 4.If you instead join
products, order_items and reviews first and then GROUP BY, the numbers inflate. Wireless Mouse has 2 order lines and 3 reviews, so the join produces 2 × 3 = 6 rows: SUM(quantity) reports 9 units instead of 3, and the review count 6 instead of 3. Office Desk and Bluetooth Speaker jump from 2 units to 4. (The average rating survives only because every review is repeated equally often.) Aggregating each one-to-many table in its own CTE before the join avoids this.The result has 8 rows because the inner join to
ratings drops Office Chair and Standing Desk, which have no reviews. Use LEFT JOIN ratings to keep all 10 products; those two then show NULL in the rating columns.| name | units_sold | avg_rating | review_count |
|---|---|---|---|
| Wireless Headphones | 3 | 5 | 1 |
| Wireless Mouse | 3 | 4 | 3 |
| Bluetooth Speaker | 2 | 4.5 | 2 |
| Coffee Maker | 2 | 3 | 1 |
| LED Desk Lamp | 2 | 4 | 1 |
| Office Desk | 2 | 4.5 | 2 |
| Notebook Set | 1 | 5 | 1 |
| USB-C Cable | 1 | 5 | 1 |
CTE vs Subquery vs Temp Table vs View
All four let you name or reuse the result of a query. They differ in how long the result lives and who can use it:
CREATE TEMP TABLE ... AS SELECT ... stores real rows that last until your session ends, so several statements can use them, and you can index them.CREATE VIEW saves the query, not its rows, permanently in the database. The query runs again every time the view is used.Are CTEs Faster Than Subqueries?
Not as a rule. A CTE changes how a query is written; how fast it runs depends on how the database executes it:
AS MATERIALIZED or AS NOT MATERIALIZED after the CTE's name overrides the choice (PostgreSQL 12 release notes).WHERE in the main query can't then shrink what the CTE reads, so the CTE may process far more rows than the final result needs.When speed matters, compare the execution plans (EXPLAIN in most databases) instead of assuming.
How a Recursive CTE Works
A recursive CTE reads its own output. It has two queries joined by UNION ALL, which stacks the rows of two queries into one result (UNION and UNION ALL):
Take the number series from the syntax block, WITH RECURSIVE numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 5) SELECT n FROM numbers. It runs like this:
SELECT 1 AS n runs once and returns one row: 1.WHERE n < 5 is false, so the step returns no rows and the recursion ends.UNION ALL stacks the rows of every step: 1, 2, 3, 4, 5.Despite the name, the database runs this as a loop, one step at a time, until a step returns nothing (PostgreSQL docs). The stop condition is what ends it, and it tests the row a step starts from: n < 5 stops at 5, while n <= 5 lets 5 through and adds a sixth row.
In PostgreSQL, MySQL and SQLite you can write UNION instead of UNION ALL: rows already in the result are then discarded, which also ends a loop that keeps producing the same values. SQL Server and Oracle accept only UNION ALL here.
Common uses:
manager_id or categories with a parent_id: the anchor selects the top rows, and the recursive member joins the table to the rows of the previous level.Recursive CTE: Fill In Months With No Orders
Show the number of orders and the revenue for every month of 2024, including months with no orders. Leave out cancelled orders.
months CTE builds the calendar: the anchor returns '2024-01-01', and each step adds a month with DATE(month_start, '+1 month') until month_start reaches '2024-12-01', giving 12 rows. The LEFT JOIN keeps every month and matches the orders placed from the first day of that month up to, but not including, the first day of the next.The status filter is the third condition in
ON, not a WHERE. In ON it only decides which orders match a month, so June, whose only order was cancelled, still appears with 0. In WHERE, o.status <> 'cancelled' would run after the join and also remove every empty month: their o.status is NULL, and a comparison with NULL is never true, so only 5 rows, January to May, would be left. Whenever a LEFT JOIN has to keep the rows with no match, filter the right-hand table in ON.A plain
GROUP BY on orders has the same gap: it returns only the months that have orders that aren't cancelled, January to May, so June to December would be missing from a chart instead of showing 0. Two details make empty months read 0: COUNT(o.id) counts only matched orders (COUNT(*) would count the empty month's own row and report 1), and COALESCE(SUM(o.total), 0) turns the NULL sum of an empty month into 0 (see NULLs in aggregates). Scroll the output to see the rest of the year.SQLite stores dates as text, so a plain string works as the anchor. In other databases, start from a real date,
CAST('2024-01-01' AS DATE), and use their date step: PostgreSQL (month_start + INTERVAL '1 month')::date, MySQL DATE_ADD(month_start, INTERVAL 1 MONTH), SQL Server DATEADD(month, 1, month_start). Always step from the 1st of a month: in SQLite, DATE('2024-01-31', '+1 month') returns '2024-03-02'.| month | order_count | revenue |
|---|---|---|
| 2024-01 | 1 | 59.98 |
| 2024-02 | 2 | 461.5 |
| 2024-03 | 3 | 493.99 |
| 2024-04 | 2 | 470.5 |
| 2024-05 | 2 | 152.99 |
| 2024-06 | 0 | 0 |
| 2024-07 | 0 | 0 |
| 2024-08 | 0 | 0 |
| 2024-09 | 0 | 0 |
| 2024-10 | 0 | 0 |
| 2024-11 | 0 | 0 |
| 2024-12 | 0 | 0 |
CTEs in PostgreSQL, MySQL, SQL Server and Oracle
The WITH syntax on this page is standard SQL and works almost unchanged everywhere. The differences are mostly about recursion:
WITH RECURSIVE is required for a recursive CTE, and a CTE can refer to one listed after it only when RECURSIVE is present. No recursion limit. generate_series() builds number and date series without recursion. PostgreSQL docsWITH RECURSIVE is required. Recursion stops with an error after 1,000 levels by default (cte_max_recursion_depth). The recursive member can't use aggregate or window functions, GROUP BY, ORDER BY or DISTINCT, and a recursive CTE's column types come from the anchor, so widen them there with CAST. MySQL docsRECURSIVE keyword: a CTE that refers to itself is recursive. A CTE can only refer to CTEs defined before it. The statement before WITH must end with a semicolon, which is why you often see ;WITH. Recursion stops with an error after 100 levels unless you add OPTION (MAXRECURSION n) to the outer query (0 means no limit, the maximum is 32,767). ORDER BY inside a CTE is rejected unless TOP or OFFSET ... FETCH is also used. SQL Server docsWITH RECURSIVE but doesn't require the RECURSIVE keyword. No recursion limit. The core library, and so SQLRide, has no generate_series() (the sqlite3 command-line shell includes it as an extension), so a recursive CTE is the portable way to build a series. SQLite docsWITH works since 11g Release 2, without a RECURSIVE keyword, but a recursive CTE needs a column list after its name. The optional CYCLE clause handles loops in the data; without it, a cycle raises an error. Oracle docsPostgreSQL, SQL Server and SQLite also accept WITH in front of INSERT, UPDATE and DELETE, not only SELECT. MySQL accepts it in front of UPDATE and DELETE, and before the SELECT in INSERT ... SELECT.
Common Mistakes
Using a CTE in a second statement
A CTE belongs to the statement that defines it. Run alone, the first statement returns 11. The second fails with no such table: customer_totals, because the CTE disappeared when the first statement ended.
Do all the work that needs the CTE inside the one statement that defines it. If several statements need the same result, store it in a temporary table or a view.
Writing WITH before every CTE
One WITH introduces all the CTEs of a statement. A second WITH at the same level is a syntax error: SQLite reports near "WITH": syntax error, and the MySQL and SQL Server docs forbid it too.
Write WITH once and put a comma between CTEs: WITH customer_totals AS (...), big_spenders AS (...) SELECT .... Leave no comma after the last CTE, or SQLite reports near "SELECT": syntax error.
Sorting inside the CTE and expecting a sorted result
An ORDER BY inside a CTE doesn't control the order of the final result. Here SQLite returns the 12 rows in order-ID order (Aarav Sharma 59.98, Sara Chen 449, Aarav Sharma 12.5, …), not by total. Without an outer ORDER BY, a database may return rows in any order it finds fastest.
Sort in the outer query: ORDER BY r.total DESC after the join puts Sara Chen (449) first and Aarav Sharma (12.5) last. Inside a non-recursive CTE, ORDER BY is only useful together with LIMIT (TOP in SQL Server), to keep the top N rows.
A recursive CTE that stops one step late, or never
The condition is checked on the row a step starts from. When n is 5, 5 <= 5 is still true, so the CTE adds one more row and returns 6 rows, 1 to 6. The same slip in the calendar example, month_start <= '2024-12-01', returns 13 months ending at 2025-01-01. With no WHERE at all, the CTE never stops: in SQLRide that freezes the browser tab (SQL Server and MySQL stop it with an error; see the database notes above).
Use WHERE n < 5, one step before the last value you want. While testing a new recursive CTE in SQLite, put a LIMIT on the recursive SELECT itself (SELECT n + 1 FROM numbers WHERE n < 5 LIMIT 100): SQLite stops adding rows once the CTE has 100, whatever the outer query does. A LIMIT on the outer query is not enough: if the outer query sorts or groups, as the calendar query does, the CTE must finish first and never does. In any database, a depth counter also works: 1 AS depth in the anchor, depth + 1 in the recursive member, and WHERE depth < 100.
Key Concepts
Pro Tip
Use CTEs for clarity, not speed: each step gets a name you can test on its own. To check a step, end the query with SELECT * FROM step_name for a moment, confirm the rows, then build the next step on top of it.
When to Use
Multi-step reports where each step builds on the last; totaling child tables before joining them; comparing every row with an average computed once; filling in missing dates so a chart has no gaps; keeping the top row per group with ROW_NUMBER; walking org charts and category trees.
Using a recursive CTE, list every month from 2024-04 to 2024-09 with the number of delivered orders (status 'delivered') placed in it, including months with none. Return two columns: month, formatted like 2024-04, and delivered_orders.