Skip to content

Common Table Expressions

Write readable complex queries with WITH ... AS and explore hierarchies with recursive CTEs.

As queries grow more complex, subqueries become hard to read. Common Table Expressions (CTEs) let you name a subquery and reference it by name. They make complex queries readable, reusable, and maintainable. MySQL 8.0 also supports recursive CTEs for traversing hierarchies and generating series.

Definition

A Common Table Expression (CTE) is a named temporary result set defined with the WITH clause. CTEs improve readability by breaking complex queries into logical blocks and can be referenced multiple times in the same query.

Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO students (name) VALUES ('Alice'), ('Bob'), ('Charlie');

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);

INSERT INTO employees (name, manager_id) VALUES
  ('Alice', NULL), ('Bob', 1), ('Charlie', 1),
  ('Diana', 2), ('Eve', 2), ('Frank', 3);

Basic CTE

A CTE starts with WITH, followed by a name, AS, and a parenthesized query. You can then reference the CTE name like a table in the main query.

Simple CTE

WITH high_scorers AS (
  SELECT student_id, course_name, score
  FROM enrollments
  WHERE score >= 80
)
SELECT s.name, h.course_name, h.score
FROM high_scorers h
JOIN students s ON h.student_id = s.id;

The CTE 'high_scorers' filters enrollments to scores >= 80. The main query joins it with students to get names. Much more readable than a subquery.

CTE vs subquery comparison

-- Without CTE (nested subquery):
SELECT s.name, sub.course_name, sub.score
FROM (
  SELECT student_id, course_name, score
  FROM enrollments WHERE score >= 80
) sub
JOIN students s ON sub.student_id = s.id;

-- With CTE (same result, cleaner):
WITH high_scorers AS (
  SELECT student_id, course_name, score
  FROM enrollments WHERE score >= 80
)
SELECT s.name, h.course_name, h.score
FROM high_scorers h
JOIN students s ON h.student_id = s.id;

Both produce the same result. The CTE version reads top-to-bottom and names the intermediate result. The subquery version requires reading inside-out.

Derived Tables

A derived table is a subquery in the FROM clause that acts as a temporary result set. You must give it an alias. Derived tables work but can be harder to read than CTEs because the logic is nested inside-out.

Derived table

SELECT sub.student_id, sub.course_count
FROM (
  SELECT student_id, COUNT(*) AS course_count
  FROM enrollments
  GROUP BY student_id
) sub
WHERE sub.course_count > 1;

The subquery in FROM creates a derived table called 'sub'. The outer query filters it. Derived tables require an alias. CTEs are usually more readable for the same logic.

When to use each:Derived Table vs CTE

Derived tables work in all SQL databases. Use them when you need portability across different database systems.

CTEs are more readable and can be referenced multiple times by name. Prefer CTEs for clarity unless you need cross-database compatibility.

Multiple CTEs

You can define multiple CTEs in a single WITH clause, separated by commas. Each CTE can reference previous CTEs or base tables.

Multiple CTEs

WITH
course_stats AS (
  SELECT course_name, COUNT(*) AS student_count,
    AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_name
),
top_courses AS (
  SELECT course_name, student_count, avg_score
  FROM course_stats
  WHERE avg_score >= 75
)
SELECT * FROM top_courses;

The first CTE computes per-course stats. The second CTE filters to high-average courses. Each CTE builds on the previous one. Clean and readable.

CTE for DML

MySQL lets you use a CTE with INSERT, UPDATE, or DELETE. The CTE identifies which rows to modify.

DELETE with CTE

WITH inactive AS (
  SELECT s.id FROM students s
  LEFT JOIN enrollments e ON s.id = e.student_id
  WHERE e.student_id IS NULL
)
DELETE FROM students
WHERE id IN (SELECT id FROM inactive);

The CTE finds students with no enrollments. The DELETE removes them. Cleaner than a nested subquery in the WHERE clause.

Recursive CTEs

A recursive CTE references itself. It has two parts: an anchor query (the starting point) and a recursive query (that joins to the CTE). MySQL repeats the recursive part until it returns no rows.

Generate a number series

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Generates numbers 1 through 10. The anchor is SELECT 1. The recursive part adds 1 each time until n reaches 10. UNION ALL combines anchor and recursive results.

Traverse an org chart

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level manager (no manager_id)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees whose manager is in the CTE
  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 * FROM org_chart ORDER BY level, name;

Starts with the top manager (level 1). Each recursion finds employees whose manager is already in the CTE, incrementing the level. Stops when no more employees are found.

Important:Recursive CTE Safety

MySQL has a cte_max_recursion_depth system variable (default 1000) that prevents infinite loops. If your recursion goes deeper than this, MySQL throws an error. You can increase it with SET SESSION cte_max_recursion_depth = 10000;

Always include a termination condition in the WHERE clause of the recursive part (e.g., WHERE n < 10).

Can you define multiple CTEs in a single WITH clause?

What is a CTE?

What makes a CTE recursive?

Key Takeaways

  • CTEs (WITH ... AS) name subqueries for readability and reuse.
  • Multiple CTEs can be defined in a single WITH clause, separated by commas.
  • CTEs can be used with SELECT, INSERT, UPDATE, and DELETE.
  • Recursive CTEs traverse hierarchies and generate series using anchor + recursive parts.
  • Always include a termination condition in recursive CTEs to avoid infinite loops.

Ready to test your knowledge?

Take a Quiz

Related Tutorials