Skip to content
Tutorials Center
My learning

Topics

Lesson 47 of 125 ยท Subqueries and Set Operations

Common Table Expressions

Learn SQL common table expressions (CTEs). Name subqueries with WITH, chain several steps, reuse a result twice and control materialization in SQLite.

A common table expression (CTE) is a named, temporary result set that lives for exactly one statement. You write it at the top of a query with the WITH keyword, give it a name, and then use that name in the main query as if it were a real table.

CTEs don't let you compute anything a subquery couldn't. What they give you is readability: instead of nesting queries three levels deep and reading them from the inside out, you write a list of small, named steps that read from top to bottom. That makes long reports easier to write, review and debug, which is why CTEs show up in almost every serious analytics query.

The examples use the company database (departments, employees, projects, assignments) and the shop database (customers, orders, order items, products).

Your first CTE

Here is the basic shape. The CTE big_earners is defined first, then the main SELECT reads from it:

SQL A first CTE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Read it like this: with a result called big_earners, defined as the following query, select these columns from it. The query returns the six people who earn at least 130,000, from the CEO down to the head of research.

A few rules are already visible:

  • WITH name AS ( query ) defines the CTE. The parentheses are required.
  • There is no semicolon after the CTE. The WITH part and the main query form one single statement.
  • The CTE only exists while this statement runs. Try adding a second statement that reads SELECT * FROM big_earners; and you'll get an error: no such table: big_earners.

Why bother? CTE vs subquery

The same report can always be written with a derived table (a subquery in FROM). Compare two versions of "employees who earn more than their department's average".

With a subquery, the helper logic sits in the middle of the query:

SQL
SELECT e.first_name, e.last_name, e.salary, ROUND(d.avg_salary) AS dept_avg
FROM employees AS e
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) AS d ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary;

With a CTE, the helper gets a name and moves to the top, so the main query reads almost like a sentence:

SQL Above the department average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Both versions return the same 10 rows, and a good database plans them the same way. The difference is for humans: you can read dept_avg on its own, test it on its own (replace the main query with SELECT * FROM dept_avg), and give it a name that explains what it is.

Debugging trick: when a CTE query returns odd results, temporarily change the final SELECT to SELECT * FROM some_cte; for each step in turn. You'll quickly see which step produces the surprise.

Naming the columns

You can list column names right after the CTE name. They replace whatever names the inner query produced:

SQL
WITH dept_size (department_id, headcount) AS (
  SELECT department_id, COUNT(*)
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_size;

That's handy when the inner query has expressions like COUNT(*) that would otherwise get awkward default names. Most people simply use AS aliases inside the query instead; both styles are standard SQL.

Chaining several CTEs

A single WITH can define many CTEs, separated by commas. Each one can read from the CTEs defined before it, so you can build a calculation as a pipeline of small steps.

This report from the shop database finds the best month of 2025 by revenue. Step one computes revenue per order line, step two sums it by month, and the main query picks the top row:

SQL A pipeline of CTEs ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

March 2025 wins with 4,514, followed by August and May. Notice the syntax: one WITH keyword at the very start, then name AS (...) blocks separated by commas, then the main query.

Common mistake: writing WITH again before the second CTE. WITH a AS (...), WITH b AS (...) is a syntax error. The keyword appears once; the comma introduces the next CTE.

Using one CTE more than once

Unlike a subquery, a CTE can be referenced several times in the same statement. That avoids copying the same logic twice.

Here monthly is used both as the list of months and, inside a scalar subquery, to compute the average month. Each month is then labeled above or below average:

SQL Referencing a CTE twice ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The average month in 2025 brings in about 2,209.66, so only five months (January, March, May, August and October) land above it. Without a CTE you would have to write the whole monthly aggregation twice.

You can also join a CTE to itself. This classic "compare each department with every other department" query uses dept_pay twice under two aliases:

SQL Joining a CTE to itself ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Design's payroll (332,000) is the smallest, so every other department appears as "bigger". Change 'Design' to 'Engineering' and the result is empty: Engineering has the largest payroll.

CTEs with INSERT, UPDATE and DELETE

In SQLite, PostgreSQL and SQL Server, a WITH clause can also sit in front of data-changing statements. The CTE prepares a set of rows, and the statement uses it in its WHERE or SELECT part.

This example copies the top earners of each department into a new table:

SQL WITH before INSERT ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The INSERT reports OK, 6 rows affected.: one leader per department, and in this data every leader is the head of that department. In SQLite the CTE goes before INSERT, UPDATE or DELETE, and the CTE itself can't be the table you modify.

PostgreSQL goes further with data-modifying CTEs: the CTE itself can be an INSERT, UPDATE or DELETE with RETURNING, and the main query reads the changed rows. A typical use is moving rows between tables in one statement:

SQL
-- PostgreSQL only: a data-modifying CTE
WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO cancelled_orders
SELECT * FROM moved;

SQLite, MySQL and SQL Server don't allow this form.

Materialization: computed once or inlined?

How does the database actually run a CTE? There are two strategies:

  • Inline it (also called "flattening"): treat the CTE like a view and merge its logic into the main query. The optimizer can then push filters down into it and use indexes.
  • Materialize it: compute the CTE once, store the rows in a temporary table, and read that table wherever the CTE is referenced.

Inlining is usually faster for a CTE used once. Materializing can win when an expensive CTE is referenced several times, or when you want to stop the optimizer from rearranging a query. SQLite and PostgreSQL (version 12 and later) decide automatically but accept a hint:

SQL MATERIALIZED hint ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The result is identical with or without the hint (Engineering, Support, Finance and Research have five or more people). The keyword only changes how the database computes it. Write NOT MATERIALIZED to ask for inlining instead. Use these hints only after measuring: the defaults are good, and you'll learn to measure in Reading Query Plans.

History lesson: before version 12, PostgreSQL always materialized CTEs, so they acted as an "optimization fence" that blocked filter push-down. Old blog posts that warn "CTEs are slow in PostgreSQL" describe that behavior, which no longer applies by default.

Scope and naming rules

  • A CTE is visible to the main query and to the CTEs that come after it in the same WITH list. It isn't visible to other statements.
  • A CTE name can shadow a real table. WITH employees AS (SELECT ...) hides the real employees table for that statement, which is legal but confusing. Pick distinct names.
  • CTEs can appear inside subqueries too, for example inside a derived table or an IN (...) list.
  • Plain CTEs can't refer to themselves. A CTE that does is a recursive CTE, the topic of the next lesson.

Limitations of CTEs

CTEs are a readability tool, not a magic one. Know these limits before you build a report around them:

  • One statement only. A CTE disappears when its statement ends. Run a second statement and the name is gone (try it below). If several statements need the intermediate result, use a view or a temporary table.
  • Not automatically faster. A CTE is organization, not an optimization. Depending on the database and version it may be inlined or materialized, so the same query can behave differently across systems. Measure with the query plan.
  • No indexes on the result. A materialized CTE is a plain temporary result, so the database can't index it the way you index a real or temporary table. A big CTE joined many times can be slower than a temporary table with an index.
  • Recursion needs care. A recursive CTE with no stopping condition runs until a limit stops it (SQL Server's default is 100 levels, MAXRECURSION; SQLite and PostgreSQL keep going until you add a LIMIT or run out of resources). See Recursive CTEs.
  • Dialect differences. Data-modifying CTEs, the MATERIALIZED hints and the RECURSIVE keyword are not available everywhere, as the dialect table below shows.
SQL A CTE lives for one statement ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first statement works and the second fails with no such table: early_orders, because the name only existed while the WITH statement ran.

CTEs compared with other tools

Tool Lifetime Reusable across statements? Typical use
Subquery / derived table One spot in one statement No A small helper used once
CTE (WITH) One statement No Multi-step reports, readable pipelines
View Permanent (until dropped) Yes A query many reports share
Temporary table Your connection/session Yes Large intermediate results, several statements

Dialect notes

CTEs are part of the SQL standard (SQL:1999) and every major database supports them today:

Database Plain CTEs Notes
SQLite 3.8.3+ MATERIALIZED / NOT MATERIALIZED hints since 3.35
PostgreSQL All current versions Data-modifying CTEs; hints since 12
MySQL 8.0+ Not available in MySQL 5.7
SQL Server 2005+ The statement before WITH must end with ;
Oracle 9i+ Docs call it "subquery factoring"

The SQL Server quirk explains why you sometimes see ;WITH in T-SQL code: because WITH has other meanings in T-SQL, the previous statement must be terminated, and some people put the semicolon right in front of WITH to be safe.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement about a CTE is true?

    A CTE is part of one statement. A second statement can't see it, which is why reusable logic belongs in a view instead.
  2. 2. How do you define two CTEs in the same query?

    Write WITH once, then separate the CTE definitions with commas. A semicolon would end the statement and throw the CTEs away.
  3. 3. In WITH x AS (...), y AS (...) SELECT ..., which can x read from?

    A CTE can use real tables and the CTEs listed before it. y can read x, but x can't read y (unless the query is recursive).
  4. 4. What does the MATERIALIZED keyword change in WITH s AS MATERIALIZED (...)?

    It's a performance hint about how the CTE is executed. The result rows are the same either way.

Summary

  • WITH name AS (query) defines a named result set that exists for one statement only.
  • CTEs make complex queries readable: a series of named steps that read top to bottom.
  • Separate several CTEs with commas; each can use the ones defined before it.
  • A CTE can be referenced several times, including joined to itself, without repeating its logic.
  • A WITH clause can precede INSERT, UPDATE and DELETE; PostgreSQL also allows data-modifying CTEs.
  • Databases either inline or materialize a CTE; SQLite and PostgreSQL accept MATERIALIZED / NOT MATERIALIZED hints.

Next: Recursive CTEs, where a CTE refers to itself to walk org charts, generate series and explore graphs.