Master Hierarchical Queries with CTEs: Recursive Techniques for Category Trees

Background

Self‑referencing tables such as a categories table, which stores an id and a parent_id, make it difficult to query parent or child paths without complex nested queries or redundant columns.

The solution is to use CTE (Common Table Expression), a named result set that can be referenced in a SELECT statement.

What Is a CTE?

All major databases support CTEs; MySQL 8 added support recently. A CTE is essentially a temporary named result set that can be used like a regular table. CTEs come in two forms: non‑recursive (simple named subqueries) and recursive (self‑referencing queries).

Non‑recursive CTE Example

WITH one AS (
    SELECT 1 AS number_one
),
two AS (
    SELECT 2 AS number_two
)
SELECT * FROM one, two;

This defines two CTEs ( one and two) and selects their Cartesian product, producing a single row with values 1 and 2.

Recursive CTE Example

WITH RECURSIVE counter AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * FROM counter;

The first SELECT creates a seed row (n = 1). The recursive part repeatedly adds 1 until n reaches 10, yielding rows 1 through 10.

Sample Data

Table categories:

CREATE TABLE categories (
    id INT,
    name VARCHAR(50),
    parent_id INT
);

Inserted rows illustrate a two‑level hierarchy with roots Root A and Root B, children Child A1, Child A2, Child B1, Child B2, and grandchildren Grandchild A1a, Grandchild A1b.

Example 1 – Find a Child and Its Sub‑tree

WITH RECURSIVE sub_tree AS (
    SELECT id, name, 1 AS relative_depth
    FROM categories
    WHERE name = 'Child A1'
    UNION ALL
    SELECT cat.id, cat.name, st.relative_depth + 1
    FROM categories cat, sub_tree st
    WHERE cat.parent_id = st.id
)
SELECT * FROM sub_tree;

Result shows Child A1 (depth 1) and its grandchildren Grandchild A1a and Grandchild A1b (depth 2).

Example 2 – Find All Ancestors of a Grandchild

WITH RECURSIVE parents AS (
    SELECT id, name, parent_id, 0 AS relative_depth
    FROM categories
    WHERE name = 'Grandchild A1b'
    UNION ALL
    SELECT cat.id, cat.name, cat.parent_id, p.relative_depth - 1
    FROM categories cat, parents p
    WHERE cat.id = p.parent_id
)
SELECT id, name, relative_depth FROM parents;

Result lists the grandchild, its parent Child A1, and the root Root A with decreasing depth values.

Example 3 – List All Roots with Their Sub‑categories

WITH RECURSIVE categories_with_roots AS (
    SELECT id, parent_id, name, name AS root_name
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT cat.id, cat.parent_id, cat.name, cwr.root_name
    FROM categories cat, categories_with_roots cwr
    WHERE cat.parent_id = cwr.id
)
SELECT name, root_name FROM categories_with_roots;

The query returns every category together with the name of its top‑level root, e.g., Child A1 → Root A, Grandchild A1a → Root A, etc.

Test Environment Preparation

Creation and insertion statements are provided above; they have been executed on both MySQL 8 and PostgreSQL 10 to verify the queries.