DocsRecipesPlaygroundQuery Doctor
Core SQLCTE (WITH)

CTE (WITH)

Name a subquery so a query reads top to bottom instead of inside out — plus recursive CTEs for hierarchies and series.

Syntax

WITH name AS (
  SELECT ...
)
SELECT ... FROM name ...;

A CTE is a subquery with a name, defined before the query that uses it. Chain several with commas.

Readability

The same logic as a nested subquery, but you read it in the order it runs:

SQL playground
Loading editor…
⌘/Ctrl + Enter

Multiple CTEs, each building on the last

SQL playground
Loading editor…
⌘/Ctrl + Enter

Recursive CTEs

For hierarchies (org charts, categories, threads) and generated series. The UNION ALL has a base case and a step that refers back to the CTE:

SQL playground
Loading editor…
⌘/Ctrl + Enter

Every recursive CTE needs a stop condition (WHERE n < 6) or it runs forever.

Gotchas

  • Not automatically faster. A CTE referenced once usually inlines like a subquery. Referenced many times, the engine may materialise it once — or may re-run it each time. Postgres ≤ 11 always materialised (an "optimisation fence"); 12+ inlines unless you write AS MATERIALIZED.
  • Scope. A CTE is visible only to the statement it's attached to.
  • RECURSIVE keyword is required in Postgres/SQLite/MySQL; SQL Server omits it.

Subqueries

Queries nested inside queries — in SELECT, FROM, and WHERE — and when a correlated one quietly runs per row.

Set operations

Combine or compare the rows of two queries — UNION, INTERSECT, EXCEPT — and why UNION ALL is usually the one you want.

On this page

SyntaxReadabilityMultiple CTEs, each building on the lastRecursive CTEsGotchas