Recursive CTE Examples: Practical Tasks

Lesson 6.6 · Reading time: ~9 min · Practice tasks ↓

Recursive CTEs solve more than org charts. In this lesson you'll apply WITH RECURSIVE to four common tasks: generating a series of dates to fill gaps in a daily report, totaling sales over every level of a category tree, exploding a bill of materials into the full list of parts, and finding all routes between two cities in a road network with cycles.

In the previous lesson you learned how a recursive CTE works: the anchor member returns the starting rows, the recursive member finds the next level, and the recursion stops when nothing new appears. Here the same pattern becomes a tool for everyday problems. Each example shows a technique you can reuse: a generated sequence, ancestor–descendant pairs, multiplied quantities, and a path with a cycle check.

The first example runs on the Sakila sample database. The others create their own tables, so run them in the playground on a database without sample data. The syntax is MySQL 8.0; it also works in MariaDB 10.2+.

1. Generating a Date Series to Fill Gaps

A report "rentals per day" built with GROUP BY shows only the days that have rentals. Days without rentals are simply missing, and a chart drawn from such data hides the gap. The fix is to generate a calendar of all days and join the data to it.

A recursive CTE generates the calendar: the anchor returns the first day, and each iteration adds one day until the last one:

WITH RECURSIVE calendar AS (
    SELECT DATE('2005-05-28') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2005-06-16'
)
SELECT c.day, COUNT(r.rental_id) AS rentals
FROM calendar AS c
LEFT JOIN rental AS r
    ON r.rental_date >= c.day
   AND r.rental_date < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

Result: 20 rows, one for each day from May 28 to June 16, 2005. From June 1 to June 13 there are no rentals, and these days show 0 instead of disappearing.

Three details matter:

• LEFT JOIN keeps the days without rentals; COUNT(r.rental_id) counts only real rentals, so empty days get 0.
• The join uses a range (>= day and < day + 1 day) instead of DATE(r.rental_date) = c.day, so an index on rental_date can still be used.
• The same pattern generates any sequence: numbers, hours, months. A series longer than 1000 steps needs a higher cte_max_recursion_depth.

2. Totals for a Whole Subtree

In a category tree, sales are recorded in specific categories, but a report needs totals for every branch: "Computers" must include laptops and desktops, and "Electronics" must include everything.

CREATE TABLE product_category (
    category_id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50) NOT NULL
);

INSERT INTO product_category VALUES
    (1, NULL, 'Electronics'),
    (2, 1, 'Computers'),
    (3, 1, 'Phones'),
    (4, 2, 'Laptops'),
    (5, 2, 'Desktops'),
    (6, 3, 'Smartphones');

CREATE TABLE sale (
    sale_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sale VALUES
    (1, 4, 1200), (2, 4, 800), (3, 5, 700),
    (4, 6, 500), (5, 6, 300), (6, 3, 150);

The trick is to build all ancestor–descendant pairs first. The anchor pairs every category with itself; each iteration adds the children of the descendants found so far:

WITH RECURSIVE subtree AS (
    SELECT category_id AS ancestor_id, category_id
    FROM product_category

    UNION ALL

    SELECT s.ancestor_id, c.category_id
    FROM subtree AS s
    INNER JOIN product_category AS c ON c.parent_id = s.category_id
)
SELECT a.name, SUM(sl.amount) AS total_sales
FROM subtree AS s
INNER JOIN product_category AS a ON a.category_id = s.ancestor_id
LEFT JOIN sale AS sl ON sl.category_id = s.category_id
GROUP BY a.category_id, a.name
ORDER BY a.category_id;

Result: Electronics 3650.00, Computers 2700.00, Phones 950.00, Laptops 2000.00, Desktops 700.00, Smartphones 800.00.

Every sale is counted once for its own category and once for each ancestor. "Phones" gets 950: 150 sold directly in this category plus 800 from "Smartphones". The aggregation happens in the final query, because MySQL doesn't allow GROUP BY or aggregate functions in the recursive member.

3. Exploding a Bill of Materials

A bill of materials (BOM) lists what a product is made of. Components have their own components, and the same part can appear in several branches. To order parts for one bicycle, you need the total quantity of each part:

CREATE TABLE part (
    part_id INT PRIMARY KEY,
    part_name VARCHAR(50) NOT NULL
);

INSERT INTO part VALUES
    (1, 'Bicycle'), (2, 'Frame'), (3, 'Wheel'),
    (4, 'Rim'), (5, 'Spoke'), (6, 'Bolt');

CREATE TABLE bom (
    parent_part_id INT,
    child_part_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (parent_part_id, child_part_id)
);

INSERT INTO bom VALUES
    (1, 2, 1),   -- a bicycle has 1 frame
    (1, 3, 2),   -- and 2 wheels
    (2, 6, 4),   -- a frame has 4 bolts
    (3, 4, 1),   -- a wheel has 1 rim
    (3, 5, 36),  -- 36 spokes
    (3, 6, 2);   -- and 2 bolts

At each level, the quantity of a component is multiplied by the quantity of its parent:

WITH RECURSIVE explosion AS (
    SELECT child_part_id AS part_id, quantity
    FROM bom
    WHERE parent_part_id = 1

    UNION ALL

    SELECT b.child_part_id, e.quantity * b.quantity
    FROM explosion AS e
    INNER JOIN bom AS b ON b.parent_part_id = e.part_id
)
SELECT p.part_name, SUM(e.quantity) AS total_quantity
FROM explosion AS e
INNER JOIN part AS p ON p.part_id = e.part_id
GROUP BY p.part_id, p.part_name
ORDER BY p.part_id;

Result: Frame 1, Wheel 2, Rim 2, Spoke 72, Bolt 8.

Spokes: 2 wheels × 36 = 72. Bolts come from two branches: 4 in the frame and 2 × 2 = 4 in the wheels, and SUM adds them up to 8. To get only the parts you buy rather than assemble, keep the rows whose part has no components of its own: add WHERE NOT EXISTS (SELECT 1 FROM bom AS c WHERE c.parent_part_id = e.part_id).

4. Routes in a Graph with Cycles

A tree has one path to each node. A road network is a graph: roads go both ways and form cycles, so a naive recursion would drive in circles forever. Let's find all routes from Berlin to Vienna:

CREATE TABLE road (
    from_city VARCHAR(30),
    to_city VARCHAR(30),
    distance_km INT NOT NULL,
    PRIMARY KEY (from_city, to_city)
);

INSERT INTO road VALUES
    ('Berlin', 'Prague', 350),
    ('Berlin', 'Leipzig', 190),
    ('Leipzig', 'Prague', 250),
    ('Prague', 'Vienna', 330),
    ('Leipzig', 'Munich', 430),
    ('Munich', 'Vienna', 400);

Each road is stored once, so a regular CTE edge first adds the reverse direction. The recursive CTE trip extends each route by one road, skips cities already on the route, and stops extending a route when it reaches Vienna:

WITH RECURSIVE edge AS (
    SELECT from_city, to_city, distance_km FROM road
    UNION ALL
    SELECT to_city, from_city, distance_km FROM road
),
trip AS (
    SELECT
        to_city AS city,
        CAST(CONCAT(from_city, ',', to_city) AS CHAR(500)) AS route,
        distance_km AS total_km
    FROM edge
    WHERE from_city = 'Berlin'

    UNION ALL

    SELECT e.to_city, CONCAT(t.route, ',', e.to_city), t.total_km + e.distance_km
    FROM trip AS t
    INNER JOIN edge AS e ON e.from_city = t.city
    WHERE FIND_IN_SET(e.to_city, t.route) = 0
      AND t.city <> 'Vienna'
)
SELECT route, total_km
FROM trip
WHERE city = 'Vienna'
ORDER BY total_km;

Result: 4 routes; the shortest one is Berlin, Prague, Vienna with 680 km.

FIND_IN_SET(e.to_city, t.route) = 0 is the cycle check from the previous lesson: a route never returns to a city it has already visited. Note that WITH RECURSIVE is written once, and the CTEs after it can be regular or recursive.

This query lists every possible route, and their number grows very quickly with the size of the network. It's fine for small graphs, but on large road networks shortest paths are found with specialized algorithms, for example in graph databases or the PostgreSQL extension pgRouting.

When a Recursive CTE Is Not the Best Tool

• Deep, frequently read trees. If a hierarchy is read much more often than it changes, store extra structure: a materialized path (/1/2/4/) or a closure table with all ancestor–descendant pairs, like the subtree CTE above, saved in a table. Then a subtree is a simple JOIN without recursion.
• Large graphs. Enumerating routes is exponential; use graph algorithms instead.
• A fixed, small number of levels. For "employee and their manager", one self-join (Lesson 5.7) is simpler.

Key Takeaways:

• A recursive CTE generates sequences: a calendar of dates joined with LEFT JOIN fills gaps in reports.
• Ancestor–descendant pairs built by a recursive CTE let you total values over a whole subtree.
• In a bill of materials, quantities are multiplied level by level and summed with GROUP BY in the final query.
• In graphs with cycles, keep the visited nodes in a path and skip them with FIND_IN_SET().
• Aggregation belongs in the final query: MySQL doesn't allow it in the recursive member.
• For large graphs or trees that are read very often, consider closure tables or graph algorithms.

Frequently Asked Questions

How do I generate a series of dates in MySQL?

Use a recursive CTE: WITH RECURSIVE calendar AS (SELECT DATE('2024-01-01') AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM calendar WHERE day < '2024-01-31') SELECT day FROM calendar;. PostgreSQL also has the built-in function generate_series().

How do I fill missing dates in a SQL report?

Generate a calendar of all dates with a recursive CTE, then LEFT JOIN the data to it and count a column of the joined table, such as COUNT(r.rental_id). Dates without data get 0 instead of disappearing.

How do I calculate a total for a node and all its children?

Build ancestor–descendant pairs with a recursive CTE: the anchor pairs each node with itself, and the recursive member adds the children of each descendant. Then join the values to the descendants and GROUP BY the ancestor.

How do I prevent cycles when traversing a graph with a recursive CTE?

Keep the path of visited nodes in a string column and add a condition such as FIND_IN_SET(next_node, path) = 0 to the recursive member. PostgreSQL 14+ also has a CYCLE clause that marks and stops cycles automatically.