Common Table Expressions

A powerful SQL construct for structuring queries, recursion, and sequence generation.

Context

Common Table Expressions (CTEs) define temporary, named result sets that exist only for the duration of a single query. They improve readability, modularity, and composability of SQL—especially for complex queries.

While they can be used to generate sequences, that’s just one of many use cases. More broadly, CTEs help:

• break complex queries into logical steps
• avoid repeating subqueries
• express recursion (hierarchies, sequences, graphs)

Syntax

with numbers (value) as (select 1)
select value from numbers;

Structure:

• WITH keyword
• CTE name (numbers)
• optional column list
• AS (...) defining the query

This produces a single-row result with value 1.

Recursive CTEs

Recursive CTEs allow a query to reference its own result. They are typically used for:

• sequences (numbers, dates)
• hierarchical data (trees, org charts)
• graph traversal

Key structure

A recursive CTE has two parts combined with UNION ALL:

• Anchor member – initial result set
• Recursive member – references the CTE itself

with recursive cte_name as (
    -- anchor
    select ...

    union all

    -- recursive
    select ... from cte_name where ...
)
select * from cte_name;

Important constraints

• A termination condition is essential (WHERE value < 100), otherwise you get infinite recursion.
• MySQL enforces a default recursion limit (cte_max_recursion_depth, default 1000).
  • See performance_schema.global_variables
• union all is preferred over union for performance (no deduplication).

Examples

Numbers sequence

with recursive numbers (value) as (
    select 1
    union all 
    select value + 1 from numbers where value < 100
)
select value from numbers;

Generates values from 1 to 100.

Dates sequence

with recursive dates (day) as (
    select '1994-07-07'
    union all 
    select adddate(day, interval 1 day) from dates where day < '1994-07-14' 
)
select day from dates;

Applications

Filling gaps in sparse data

Event-driven tables (e.g. sales, logs) often miss rows for “empty” periods. This leads to misleading reports. CTEs can generate a complete domain (e.g. all dates), which is then left-joined with actual data.

with recursive dates (day) as (
    select '2026-01-01'
    union all 
    select adddate(day, interval 1 day) from dates where day < '2026-01-31'
)
select day, coalesce(sales.sales_number, 0) from dates 
left join sales on dates.day = sales.date;

Note usage of coalesce function which takes any number of nullable values and returns the first non-nullable value.