This lesson on Recursive CTEs is hands-on and example-driven. You will master Common Table Expressions (CTEs) to replace deeply nested subqueries, reference modular datasets multiple times within a single query, and construct recursive CTEs to generate date sequences and fill gaps in time-series data.
What You'll Be Able To Do
- Refactor complex nested subqueries into readable, modular CTE definitions.
- Define and join multiple independent CTE blocks within a single WITH statement.
- Reference the same CTE repeatedly across main query filters and calculations.
- Evaluate trade-offs between CTEs, subqueries, temporary tables, and database views.
- Construct recursive CTEs using anchor and recursive members combined via UNION ALL to generate continuous date series.
Detailed Concept Walkthrough
1. CTE Basics and Subquery Refactoring
A Common Table Expression (CTE) defines a named, temporary result set positioned above the main query using the WITH keyword. It converts deeply nested subquery logic into a sequential, top-to-bottom reading structure.
- Syntax Rule: The WITH clause introduces the CTE, giving the subquery an alias (e.g.,
WITH cte_name AS (...)) that can be queried immediately below as if it were a physical table. - Under the Hood: In modern SQL engines like MySQL 8.0+, CTEs exist only for the scope of the single statement and are evaluated inline during statement execution.
- Best Practice: Draft initial analytical transformations with simple subqueries first if faster, then refactor them into CTEs to clean up production-grade code.
-- Compute categories whose maximum item price is under $15
WITH mp AS (
SELECT category, MAX(price) AS max_price
FROM menu_items
GROUP BY category
)
SELECT COUNT(*)
FROM mp
WHERE max_price < 15;
Key Takeaway: CTEs place query logic up front, converting nested subquery nesting into clean, linear SQL.
2. Multiple References and Chained CTEs
CTEs eliminate duplicate subquery definitions by allowing a single named expression to be referenced multiple times or chained together with other CTEs using a comma-separated list.
- Mechanism: You only declare the WITH keyword once at the top; subsequent CTE definitions are chained using commas (e.g.,
WITH cte1 AS (...), cte2 AS (...)). - Execution Flow: A CTE defined at the top can be referenced in multiple places inside the main query, such as in the FROM clause, JOIN conditions, or inside scalar subqueries.
- Under the Hood: Unlike raw subqueries that force you to re-type the exact same query text in multiple places, a single CTE name represents the entire logic throughout the statement.
-- Chaining multiple CTEs and joining them in the main query
WITH mp AS (
SELECT category, MAX(price) AS max_price
FROM menu_items
GROUP BY category
),
ci AS (
SELECT *
FROM menu_items
WHERE item_name LIKE '%chicken%'
)
SELECT *
FROM ci
JOIN mp ON ci.category = mp.category;
Key Takeaway: Define multiple CTEs in a single WITH statement separated by commas to join or cross-reference transformed subsets cleanly.
3. Recursive CTE Architecture
A recursive CTE references itself repeatedly to generate sequential data or traverse hierarchies until a specific termination condition is reached.
- Mechanism: A recursive CTE requires the
WITH RECURSIVEdeclaration and consists of two parts connected byUNION ALL: an initial base query (the anchor member) and an iterative query referencing the CTE itself (the recursive member). - Execution Flow: The anchor member executes first to produce the starting row (e.g., '2023-06-01'); the recursive member then runs iteratively, incrementing the value until the WHERE termination condition evaluates to false.
- Best Practice: Always define an explicit termination condition in the recursive step (e.g.,
WHERE dt < '2023-06-06') to prevent infinite looping.
-- Generate a continuous date series to fill missing dates
WITH RECURSIVE date_series AS (
SELECT CAST('2023-06-01' AS DATE) AS dt -- Anchor member
UNION ALL
SELECT dt + INTERVAL 1 DAY -- Recursive member
FROM date_series
WHERE dt < '2023-06-06' -- Termination check
)
SELECT *
FROM date_series;
Key Takeaway: Recursive CTEs use an anchor query and an iterative self-referencing query tied with UNION ALL to generate series or traverse networks.
4. Time-Series Gap Filling and Imputation
Combining recursive CTE date series with window functions allows you to identify missing observations and impute values from previous records.
- Mechanism: LEFT JOIN the generated date series CTE to the raw, gapped transaction table on date columns to retain every required date in the sequence.
- Under the Hood: Missing dates produce NULL values for metric columns; applying
COALESCEwith theLAG()window function carries forward the most recent non-null value. - Execution Flow: The recursive CTE creates the full date spine, the JOIN aligns existing stock prices, and
LAG(price) OVER ()extracts the prior row's value to replace NULLs.
-- Fill missing dates and impute missing stock prices
WITH RECURSIVE date_series AS (
SELECT CAST('2023-06-01' AS DATE) AS dt
UNION ALL
SELECT dt + INTERVAL 1 DAY
FROM date_series
WHERE dt < '2023-06-06'
)
SELECT
d.dt,
COALESCE(s.price, LAG(s.price) OVER (ORDER BY d.dt)) AS price
FROM date_series d
LEFT JOIN stock_prices s ON d.dt = s.date;
Key Takeaway: Left join a generated recursive date spine against sparse data to expose gaps, then impute nulls using LAG or COALESCE.
Topics Covered in Recursive CTEs
- CTE Concept Overview (0:00 - 0:45) — The instructor introduces CTEs, course goals, and the menu items demonstration table.
- Subquery to CTE Refactor (0:45 - 2:15) — A nested subquery filtering maximum category prices is restructured into a basic CTE.
- Multiple CTE References (2:15 - 3:45) — The single CTE is queried multiple times within the outer query to compute aggregate comparisons.
- Chaining Multiple CTEs (3:45 - 5:15) — Multiple distinct CTE blocks are declared with a single WITH clause and joined together.
- CTEs vs. Temporary Tables (5:15 - 6:10) — Performance, caching, and permission requirements are contrasted between CTEs and temp tables.
- Recursive CTE Architecture (6:10 - 7:40) — A recursive CTE generates a sequential date series using an anchor, UNION ALL, and a recursive step.
- Imputing Missing Data (7:40 - 8:45) — The date spine is joined to stock prices using COALESCE and LAG window functions to fill nulls.
SQL Cheat Sheet
-
WITH cte_name AS (...)— Defines a single temporary named result setWITH mp AS (SELECT category, MAX(price) AS mx FROM menu_items GROUP BY category) SELECT * FROM mp; -
WITH cte1 AS (...), cte2 AS (...)— Chains multiple distinct CTE definitions with commasWITH c1 AS (SELECT * FROM menu_items), c2 AS (SELECT * FROM menu_items) SELECT * FROM c1, c2; -
WITH RECURSIVE cte AS (anchor UNION ALL recursive)— Generates data iteratively via self-referencing query logicWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 5) SELECT * FROM seq; -
UNION ALL— Stacks row outputs from anchor and recursive membersSELECT '2023-06-01' AS dt UNION ALL SELECT '2023-06-02' AS dt; -
COALESCE(col, fallback)— Returns the first non-null argumentSELECT COALESCE(s.price, 600) FROM stock_prices s; -
LAG(col) OVER (ORDER BY col)— Fetches a value from the preceding rowSELECT LAG(price) OVER (ORDER BY date) FROM stock_prices;
Comparison Table
| Feature / Tool | Common Table Expressions | Temporary Tables | Subqueries |
|---|---|---|---|
| Scope & Lifetime | Single query statement execution | Entire database session / connection | Inline expression execution only |
| Multiple References | Yes, within statement | Yes, across entire session | No, requires duplicate SQL |
| Permissions Needed | Read-only SELECT permissions | CREATE TEMP TABLE privileges | Read-only SELECT permissions |
| Primary Use Case | Query readability and recursion | Heavy repetitive complex computations | Quick, one-off inline filtering |
Common Pitfalls
- Mistake: Repeating the WITH keyword before every CTE definition. Avoid: Declare WITH only once and separate individual CTE blocks with commas.
- Mistake: Omitting the termination condition in the recursive CTE member. Avoid: Include a strict WHERE clause in the recursive query to prevent infinite recursion.
- Mistake: Using temporary tables when read-only permissions are enforced. Avoid: Use CTEs or collaborate with data engineers to materialize results as views.
FAQs
- When should I choose a CTE over a temporary table? Use CTEs for single-query readability and data pipeline transformations; use temporary tables when caching expensive intermediate computations reused across multiple independent queries.
- Why did my CTE fail on older MySQL database versions? MySQL introduced CTE support in version 8.0 (2018); on older versions, you must rely on subqueries or temporary tables.
- Can a CTE reference another CTE defined in the same WITH clause? Yes, CTEs defined later in the WITH block can query previously defined CTEs located earlier in that same statement.