Learn SQL  /  Advanced Queries  /  Common Table Expressions (CTE)

Common Table Expressions (CTE)

A Common Table Expression (CTE), written with WITH, names a temporary result set that the rest of the query can reference — making complex, multi-step queries far easier to read than nested subqueries.

Basic CTE

R Code
WITH class_summary AS (
  SELECT class, AVG(score) AS avg_score
  FROM students
  GROUP BY class
)
SELECT *
FROM class_summary
WHERE avg_score > 75;
Output
class | avg_score
A     | 90.0

This is the exact same result as the FROM-subquery example on the Subqueries page - CTEs and subqueries can often do the same job, but CTEs read top-to-bottom like a set of named steps, which is much easier to follow in longer queries.

Multiple CTEs in One Query

R Code
WITH
class_avg AS (
  SELECT class, AVG(score) AS avg_score FROM students GROUP BY class
),
top_class AS (
  SELECT class FROM class_avg ORDER BY avg_score DESC LIMIT 1
)
SELECT s.name, s.score
FROM students s
JOIN top_class t ON s.class = t.class;
Output
name  | score
Rafi  | 88
Meera | 92

CTE vs Subquery — When to Prefer Each

💡 Tip
Use a CTE when a query has several logical steps that benefit from a clear name (class_avg, top_class), or when the same intermediate result is referenced more than once. Use a plain subquery for a single, simple, one-off filter.

Advanced: Recursive CTE

🚀 Advanced Example
A recursive CTE references itself, letting you walk a hierarchy - like an org chart of managers and their reports - to any depth in one query.
R Code
WITH RECURSIVE org_chart AS (
  -- anchor: the top-level manager (no manager_id)
  SELECT emp_id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive step: find employees reporting to the previous level
  SELECT e.emp_id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.emp_id
)
SELECT * FROM org_chart;
Output
emp_id | name  | manager_id | depth
1      | Rafi  | NULL       | 1
2      | Meera | 1          | 2
3      | Zayn  | 1          | 2

Key Points

  • WITH name AS (query) defines a Common Table Expression - a named, temporary result used later in the query.
  • Multiple CTEs can be chained, each one able to reference the ones defined before it.
  • CTEs improve readability over nested subqueries for multi-step logic, and can be referenced more than once.
  • WITH RECURSIVE lets a CTE reference itself, ideal for hierarchical data like org charts or category trees.