intermediateAdvanced SQL100% Free

SQL CTEs (Common Table Expressions)

Definition

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword that exists only for the duration of a single SQL statement.

Introduction to SQL CTEs (Common Table Expressions)

CTEs make complex SQL readable by breaking it into named logical steps. Instead of nesting multiple subqueries, you define each step with a meaningful name. CTEs also enable recursive queries for hierarchical data like organizational charts, bill of materials, or tree structures.

Syntax

SQL

WITH cte_name AS (
  SELECT ...
),
another_cte AS (
  SELECT ... FROM cte_name
)
SELECT * FROM another_cte;

Examples

Simple CTE to Improve Readability

WITH high_earners AS (
  SELECT name, department, salary
  FROM employees
  WHERE salary > 80000
)
SELECT department,
  COUNT(*) AS high_earner_count,
  AVG(salary) AS avg_high_salary
FROM high_earners
GROUP BY department;

The CTE 'high_earners' creates a named subset, then the main query aggregates it. Much clearer than a nested subquery.

Try in Playground

Recursive CTE — Organizational Hierarchy

WITH RECURSIVE org_chart AS (
  -- Anchor: start with top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: add each employee's direct reports
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT name, level FROM org_chart ORDER BY level;

Recursive CTEs traverse hierarchical data. The anchor query starts at the top level, the recursive part joins each employee to their manager from the previous iteration.

Try in Playground

Common Mistakes

Thinking CTEs are materialized (they usually aren't — they may be re-evaluated multiple times)

Not using RECURSIVE keyword when writing recursive CTEs

Missing the termination condition in recursive CTEs — causes infinite loops

Using CTEs when a simple subquery would suffice — adds visual complexity without benefit

Frequently Asked Questions

What is a CTE in SQL?

A CTE (Common Table Expression) is a named temporary result set created with the WITH keyword. It simplifies complex queries by letting you define intermediate results with meaningful names. CTEs exist only for the duration of the query and cannot be referenced outside it.

What is a recursive CTE?

A recursive CTE references itself. It has two parts: an anchor query (the base case, evaluated once) and a recursive query (joins the CTE to itself to add rows iteratively). Used for hierarchical data: org charts, file trees, bill of materials. Requires the RECURSIVE keyword in PostgreSQL and some other databases.

Are CTEs faster than subqueries?

Not necessarily. In most databases, CTEs are NOT automatically materialized — they may be inlined as subqueries and evaluated multiple times. PostgreSQL 12+ added MATERIALIZED keyword to force materialization when beneficial. Always check EXPLAIN output to confirm behavior.

Can you use multiple CTEs in one query?

Yes. Define multiple CTEs separated by commas: WITH cte1 AS (...), cte2 AS (...) SELECT ... Each CTE can reference previously defined CTEs in the same WITH clause.

Practice This in the SQL Playground

Write real queries, see live results, and master SQL CTEs (Common Table Expressions) hands-on. 100% free.

Open SQL Playground Browse All Lessons