SQL Academy · Lesson

Walking a Category Tree

Expand parent-child trees fully.

Walking a Category Tree is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Category Tree?

Many real-world datasets have a parent-child relationship. A product catalog may have categories like Electronics → Phones → Smartphones. Each node has a parent, forming a tree structure.

In SQL, this is typically stored as a self-referencing table: each row has an id and a parent_id that points to another row in the same table.

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

Sample Category Data

Let us populate a small category tree. The root node has parent_id = NULL because it has no parent. Every other node points to its parent with a non-null parent_id.

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

The Problem with Simple Queries

A plain SELECT can only fetch one level at a time. To reach three levels deep you would need three separate queries or three self-joins, which becomes unmanageable as the tree grows.

WITH RECURSIVE solves this by letting a query reference its own output, walking level by level until no new rows are found.

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

Anatomy of WITH RECURSIVE

A recursive CTE has two parts separated by UNION ALL:

1. Anchor member — a normal SELECT that provides the starting rows.

2. Recursive member — a SELECT that joins the CTE back to itself, producing the next level on each iteration.

The engine repeats the recursive member until it returns zero rows.

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

Walking the Full Tree from the Root

Start at the root (where parent_id IS NULL) and walk down to every descendant. The recursive member joins each accumulated row back to categories on the parent-child relationship.

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

Tracking the Path

It is helpful to record the full path from root to each node. We can build a path string by concatenating ancestor names as we recurse deeper.

This makes it easy to display breadcrumb trails like Electronics / Phones / Smartphones.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

Starting from a Specific Node

You do not have to start from the root. By changing the anchor's WHERE clause you can walk the subtree of any node. Here we start from Phones (id = 2) and retrieve all its descendants.

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

Walking Upward: Finding All Ancestors

The tree can also be walked in reverse — upward from a leaf to the root. Simply flip the join so that you follow parent_id upward instead of downward. This is useful when you need the full breadcrumb for a known leaf node.

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

Adding an Indented Display

A common UI pattern is to indent child nodes visually. We can use REPEAT (or LPAD) together with the depth column to prefix each name with spaces, producing a text-based tree view.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

Guarding Against Infinite Loops

If your data contains a cycle (A is parent of B, B is parent of A) the recursion will run forever and crash. You can guard against this by tracking visited IDs in an array and stopping when the current ID is already present.

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

Counting Descendants per Node

Once you have the full tree, you can aggregate it. Here we count how many descendants each node has by grouping the child rows back against the ancestor list. This is useful for showing item counts next to category names in a navigation menu.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, id AS root_id
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

Quick Check

Test your understanding of recursive category tree queries.

Lesson Recap

In this lesson you learned how to walk a self-referencing category table using WITH RECURSIVE.

Key takeaways:

- The anchor member selects the starting nodes (usually the root).

- The recursive member joins the CTE back to the base table to find the next level.

- Add a depth column to track how many levels deep each node is.

- Build a path string to generate breadcrumb trails.

- Walk upward by following parent_id in reverse to find all ancestors.

- Use a visited array to protect against cycles in dirty data.

Frequently asked questions

Is the “Walking a Category Tree” lesson free?

Yes — the full text of “Walking a Category Tree” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Walking a Category Tree”?

Expand parent-child trees fully. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Walking a Category Tree” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy