Skip to content
Tutorials Center
My learning

Topics

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.

SQL
WITH expensive AS (
  SELECT * FROM products WHERE price > 100
)
SELECT name FROM expensive;

Try it

SQL Two chained CTEs ยท database: shop

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

SQL Using one CTE twice ยท database: company

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

Syntax

SQL
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)
SQL
-- 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 JOIN is 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.