Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 40 ยท Joins and Subqueries

Recursive CTEs and Hierarchical Queries in MariaDB

Walk trees and hierarchies in MariaDB with WITH and WITH RECURSIVE: org charts, category paths, bill of materials, number and date series, cycle protection and the recursion limit.

Some data points to itself: an employee has a manager who is also an employee, a category has a parent category, a comment has a reply. To ask "who reports to Maria, directly or indirectly?" you would need to join the table to itself an unknown number of times. A recursive common table expression (CTE) solves this in one query. MariaDB supports WITH since 10.2 and WITH RECURSIVE as well, while older MySQL versions had neither.

Plain CTEs first

A CTE names a subquery at the top of a statement, so the rest of the statement reads cleanly:

SQL
WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT o.id, o.status, t.total
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.id
WHERE t.total > 200;
SQL A non-recursive CTE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The recursive pattern

A recursive CTE has two parts joined by UNION or UNION ALL:

  1. an anchor member: a normal query that produces the starting rows;
  2. a recursive member: a query that refers to the CTE itself and produces the next level.

MariaDB runs the anchor once, then repeats the recursive member using the rows from the previous round, until a round produces no new rows. Then everything is returned.

flowchart TD
  A["Anchor: the CEO"] --> R1["Round 1: reports of the CEO"]
  R1 --> R2["Round 2: their reports"]
  R2 --> R3["Round 3: ... until none are left"]

Each round feeds the next.

Walking an org chart

The sample company table has manager_id pointing to another employee, with NULL for the top person:

SQL Everyone and their depth in the hierarchy ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The depth column counts the levels. Every row of chain is an employee, and the recursion stops by itself when no employee has a manager in the previous level.

Building a path

Carry text along the recursion to show the whole chain of command. In MariaDB use CONCAT; the standard || works in SQLite:

SQL The reporting path of each employee ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Going up instead of down

Start from one row and climb to the root to list all ancestors, for example the management chain above a given employee:

SQL All managers above employee 20 ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Series of numbers and dates

Without a table to walk, recursion generates sequences, such as every day of a month or a numbers table for joins and tests:

SQL One row per day

Press Run (or Ctrl + Enter) to see the result here.

A calendar built this way, left-joined to your data, makes reports show days with no activity as zero instead of leaving gaps:

SQL Orders per day including empty days ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For plain number series, MariaDB's seq_1_to_N virtual tables are even shorter (SELECT seq FROM seq_1_to_10).

Computing with recursion: factorials and Fibonacci

The recursive member can carry running values, which makes small algorithms possible:

SQL Factorials with recursion

Press Run (or Ctrl + Enter) to see the result here.

Stopping runaway recursion

If the data contains a cycle (A manages B, B manages A) or you forget the stop condition, the recursion never ends. MariaDB protects you with a limit:

SQL
SET max_recursive_iterations = 1000;      -- default is large (4,294,967,295 in MariaDB)

Set a sensible value for your session, and design for safety:

  • Add a depth limit in the recursive member: WHERE c.depth < 20.
  • With UNION (not UNION ALL) duplicate rows are discarded, which stops simple cycles, because a repeated row adds nothing new.
  • Track the visited path and refuse to revisit a node.
  • Keep a data constraint so that a node cannot be its own ancestor.

Practical tips

  • Use UNION ALL unless you need deduplication; it is faster.
  • The recursive member may reference the CTE only once, and cannot use aggregates, DISTINCT or LIMIT on the recursive reference.
  • The column types come from the anchor: if the anchor gives VARCHAR(10) but later rows are longer, MariaDB may truncate. Cast in the anchor (CAST(name AS CHAR(500))).
  • Index the foreign key (manager_id, parent_id), so each round is an index lookup, not a scan.
  • Alternatives for large trees that are read often: nested sets, materialized paths or a closure table; those trade write complexity for fast reads.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What are the two parts of a recursive CTE?

    The anchor provides starting rows; the recursive member repeatedly derives the next rows from the previous ones.
  2. 2. When does the recursion stop?

    The engine repeats until the recursive member produces nothing, or until an iteration limit is hit.
  3. 3. How can you guard against a cycle in the data?

    A depth condition or path tracking guarantees termination.
  4. 4. In an org chart, what does the anchor query usually select?

    The recursion starts at the root and walks down to the reports.
  5. 5. Which MariaDB setting limits the recursion?

    max_recursive_iterations caps the number of rounds so that runaway queries stop.

Summary

  • A CTE names a subquery; WITH RECURSIVE adds an anchor plus a recursive member that refers to the CTE.
  • Use it for org charts, category trees, bills of materials, paths, and series of numbers or dates.
  • Carry depth or path columns, and guard against cycles with a depth limit, UNION or visited tracking.
  • Index the parent key and set max_recursive_iterations as a safety net.

Next: String Functions in MariaDB