Common Table Expressions — WITH

11 min read

Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword before the main query. You can think of it as a named subquery that lives only for the duration of the statement. CTEs make complex queries far more readable by breaking them into labelled steps.

sql

-- Step 1: find average score per course
-- Step 2: find courses whose average exceeds 70
WITH course_avg AS (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrolments
  WHERE score IS NOT NULL
  GROUP BY course_id
)
SELECT c.title, ca.avg_score
FROM course_avg ca
INNER JOIN courses c ON c.id = ca.course_id
WHERE ca.avg_score > 70
ORDER BY ca.avg_score DESC;

Chaining Multiple CTEs

You can define several CTEs in a single WITH block, separated by commas. Each CTE can reference CTEs defined before it in the same block.

sql

WITH
  enrolled_students AS (
    SELECT DISTINCT student_id FROM enrolments
  ),
  active_students AS (
    SELECT s.id, s.name
    FROM students s
    INNER JOIN enrolled_students es ON es.student_id = s.id
  )
SELECT name FROM active_students ORDER BY name;

CTEs and subqueries are often interchangeable. Prefer CTEs when a logical step is reused more than once in the same query, or when nesting subqueries makes the SQL hard to read. Some databases also support recursive CTEs for traversing tree-shaped data.

Ready to test yourself?

Practice SQL— quiz & coding exercises