SQL Academy · Lesson

Walking a Category Tree

Expand parent-child trees fully.

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);

All lessons in this course

← Back to SQL Academy