Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 40 ยท Functions and Window Functions

CTEs and Recursive Queries in MariaDB

Use MariaDB common table expressions (WITH) to structure queries, and write recursive CTEs for org charts, category trees, number series and loop-safe hierarchy walks.

A common table expression (CTE) is a named, temporary result set defined with WITH at the top of a statement. It works like a view that exists for one query and makes long statements readable: name each step, then build on it. A recursive CTE refers to itself, which is how SQL walks hierarchies of unknown depth such as org charts, category trees and bill-of-materials.

MariaDB supports non-recursive and recursive CTEs from version 10.2.2. All examples below run in the in-browser engine.

A first CTE

SQL
WITH dept_totals AS (
  SELECT department_id, SUM(salary) AS payroll, COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT d.name, t.payroll, t.headcount
FROM dept_totals t
JOIN departments d ON d.id = t.department_id
ORDER BY t.payroll DESC;

You can define several CTEs separated by commas; a later one may use an earlier one:

SQL Chained CTEs ยท database: company

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

Compared with a derived table (FROM (SELECT ...) x), a CTE can be referenced more than once in the statement, reads top to bottom, and can be recursive. Compared with a view, it is not stored and vanishes at the end of the statement. CTEs can also be used in INSERT ... SELECT, UPDATE and DELETE in MariaDB.

MariaDB treats a non-recursive CTE much like a derived table or view and merges it into the outer query when it can, so a CTE is not an optimization barrier by itself. It is a readability tool.

Recursive CTEs

A recursive CTE has two parts joined with UNION ALL (or UNION):

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

Evaluation repeats the recursive member on the rows added in the previous round until it produces no new rows.

SQL
WITH RECURSIVE name (columns) AS (
  SELECT ...            -- anchor
  UNION ALL
  SELECT ... FROM name JOIN ...   -- recursive member
)
SELECT * FROM name;

(MariaDB accepts WITH RECURSIVE; plain WITH also works when the CTE is recursive.)

Generating a series

SQL Numbers and dates from nothing

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

This is the standard way to produce a calendar table, fill gaps in a report (left join your data to the series) or create test data. In MariaDB you can also use the built-in sequence tables seq_1_to_7 when the SEQUENCE engine is installed (SELECT seq FROM seq_1_to_7;), which is shorter but MariaDB-only.

Walking an org chart

The employees table points each person to a manager_id. A recursive CTE lists everyone below a manager with their depth and path:

SQL
WITH RECURSIVE chain AS (
  SELECT id, first_name, manager_id, 0 AS depth, CAST(first_name AS CHAR(200)) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, c.depth + 1, CONCAT(c.path, ' > ', e.first_name)
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT CONCAT(REPEAT('  ', depth), first_name) AS org, path
FROM chain
ORDER BY path;

Note the CAST(... AS CHAR(200)) in the anchor: the column type of a recursive CTE is fixed by the anchor, so a path starting as a short string would be truncated in later rounds in strict mode (or raise an error). Cast it wide enough.

SQL Org chart with depth and path ยท database: company

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

Going up: all ancestors

Reverse the join to climb from one employee to the top, for example to print the reporting line:

SQL Reporting line of one employee ยท database: company

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 your condition never becomes false, the recursion never ends. Protect yourself in three ways:

  • a WHERE depth < N condition in the recursive member;
  • track the visited path and refuse to revisit (WHERE c.path NOT LIKE CONCAT('%/', e.id, '/%'));
  • set the server variable max_recursive_iterations to a sane cap on shared servers (check SHOW VARIABLES LIKE 'max_recursive_iterations' for your version's default), so one bad query cannot loop for hours.

UNION (without ALL) removes duplicate rows at every step, which also stops simple cycles whose rows repeat exactly, but it costs a de-duplication pass. Use UNION ALL plus an explicit guard on large data.

Hierarchies in schema design

An id/parent_id table (an adjacency list) plus recursive CTEs is the simplest design and is fully served by MariaDB 10.2.2+. Alternatives such as materialized paths, nested sets and closure tables trade easier reads for harder writes; see the SQL tutorial on trees. For most applications, the adjacency list with a recursive CTE is enough, provided parent_id is indexed and has a foreign key.

Common mistakes

  • Putting a non-recursive reference first: the anchor must not mention the CTE.
  • Different column count or type between anchor and recursive member.
  • Missing termination condition.
  • Using ORDER BY/LIMIT inside the recursive member (not allowed in standard form); sort in the outer query.
  • Expecting a CTE to be cached across several statements: it is only valid for one statement.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

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

    The anchor provides the starting rows; the recursive member repeatedly uses the previous round's rows to produce new ones.
  2. 2. What ends the evaluation of a recursive CTE?

    Iteration stops when a round produces no rows. A bad condition or cyclic data prevents that, so guard against it.
  3. 3. How long does a CTE exist?

    A CTE is not stored; use a view or temporary table for reuse across statements.
  4. 4. Why do you CAST the path column in the anchor of a string-building recursive CTE?

    The result column takes its type from the anchor, so give it room to grow.

Summary

  • WITH name AS (...) names a query step for one statement; chain several with commas.
  • Recursive CTEs (anchor + UNION ALL + recursive member) walk hierarchies and generate series.
  • Always guard recursion with a depth limit, a visited-path check or max_recursive_iterations.
  • Adjacency list + recursive CTE is the practical default for trees in MariaDB.

Next: Views and Updatable Views