SQL Reference ยท Statements and Clauses
SQL WITH clause (CTE)
Reference for the SQL WITH clause and common table expressions: name subqueries, chain several CTEs, reuse them, and dialect differences and pitfalls.
A common table expression (CTE) gives a query a name. You write it with WITH name AS (query) before the main statement, and then use name like a table. CTEs make long queries easier to read by splitting them into steps, and they let you refer to the same intermediate result several times.
A CTE exists only for the statement it belongs to. For recursive queries (trees, sequences), see WITH RECURSIVE.
WITH expensive AS ( SELECT * FROM products WHERE price > 100 ) SELECT name FROM expensive;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
WITH cte_name [ (column [, ...]) ] AS ( select_statement ) [ , another_name AS ( select_statement ) ] main_statement;
| Part | Description |
|---|---|
cte_name |
The name used in the main query. |
(column, ...) |
Optional list that names the CTE's columns. |
| Several CTEs | Separate with commas; later ones may refer to earlier ones. |
| Main statement | SELECT, and in most dialects also INSERT, UPDATE, DELETE or MERGE. |
Returns: whatever the main statement returns. The CTE itself produces no output.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
Basic WITH |
3.8.3+ | 8.4+ | 8.0+ | 2005+ | 9i R2+ |
MATERIALIZED / NOT MATERIALIZED hint |
3.35+ | 12+ | No | No | /*+ MATERIALIZE */ |
WITH before INSERT/UPDATE/DELETE |
Yes | Yes (and data-modifying CTEs) | Yes | Yes | Limited |
| Keyword for recursion | WITH RECURSIVE |
WITH RECURSIVE |
WITH RECURSIVE |
WITH (no RECURSIVE) |
WITH (no RECURSIVE) |
-- PostgreSQL 12+ and SQLite 3.35+: control inlining WITH recent AS MATERIALIZED ( SELECT * FROM orders WHERE ordered_at >= '2025-06-01' ) SELECT count(*) FROM recent;
Usage notes
- Readability, not speed. Most engines inline a CTE used once, so it runs like a subquery. PostgreSQL before 12 always materialized it, which could be slow or fast depending on the query.
- A CTE is not stored. If the main query uses it three times, the engine may compute it up to three times unless it is materialized.
- Scope. A CTE is visible only in its own statement and cannot be indexed.
- Name clashes: a CTE with the same name as a real table hides that table inside the statement.
- Column names come from the inner query unless you give a column list. Alias expressions inside it.
- Do not use a CTE where a plain
JOINis clearer. Chains of three or four tiny CTEs can be harder to follow than one flat query. - SQL Server needs the previous statement to end with a semicolon before
WITH.
Related
WITH RECURSIVE, derived tables,SELECT,VALUES- Lessons: CTEs, Recursive CTEs, Views