How do I store and query tree data (categories, comments, org charts) in SQL?

Categories with subcategories. Comment threads. An org chart. Folders. Sooner or later every application needs a tree, and relational tables are flat. How should you store it, and how do you query "everything under this node" without a loop that runs one query per level?

There are four classic models. With recursive CTEs now supported everywhere, the simplest one is usually the right default.

1. Adjacency list: each row points to its parent

CREATE TABLE category (
    id        int PRIMARY KEY,
    parent_id int REFERENCES category (id),   -- NULL = root
    name      text NOT NULL
);
CREATE INDEX ON category (parent_id);

It's easy to understand and easy to change: moving a subtree is a single UPDATE of one parent_id. The traditional complaint was that you couldn't fetch a whole subtree in one query. Recursive CTEs solve that:

WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM category
    WHERE id = 42                      -- start node
  UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM category c
    JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY depth;

This works in PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.8.3+ and Oracle. SQL Server uses the same query without the RECURSIVE keyword. Swap the join to c.id = s.parent_id to walk up to the ancestors (breadcrumbs).

Guard against cycles. Bad data (A → B → A) makes the recursion run until it hits a limit. PostgreSQL 14+ has a built-in clause:

WITH RECURSIVE subtree AS (...)
CYCLE id SET is_cycle USING path
SELECT * FROM subtree WHERE NOT is_cycle;

Elsewhere, track a path column and stop when an id repeats, or cap depth. SQL Server stops at 100 levels by default (OPTION (MAXRECURSION n)).

2. Materialized path: store the whole ancestry

id | path
 1 | 1
 4 | 1/4
 9 | 1/4/9

A subtree is a prefix search, which a B-tree index handles well:

SELECT * FROM category WHERE path LIKE '1/4/%';

Reads are very fast and depth comes from counting separators. Moving a subtree means rewriting the path of every descendant. PostgreSQL's ltree extension provides a proper type with GiST indexes and operators (path <@ '1.4'). SQL Server's hierarchyid type is the same idea built in.

It's a good fit for trees that are read much more than they're restructured, like categories or file paths.

3. Nested sets: left and right numbers

Each node stores lft and rgt values from a depth-first walk. A subtree is everything between them:

SELECT child.*
FROM category parent
JOIN category child ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.id = 4;

Reading subtrees is very fast, but inserting or moving one node renumbers about half the table, and concurrent writes contend heavily. It was popular before recursive CTEs existed. Today it's only worth it for large, nearly static trees.

4. Closure table: store every ancestor–descendant pair

CREATE TABLE category_closure (
    ancestor   int NOT NULL,
    descendant int NOT NULL,
    depth      int NOT NULL,
    PRIMARY KEY (ancestor, descendant)
);

Every node has a row for itself (depth 0) plus one for each ancestor. Subtrees, ancestors and "direct children only" are all simple indexed joins:

SELECT c.* FROM category c
JOIN category_closure cc ON cc.descendant = c.id
WHERE cc.ancestor = 4;

It handles deep trees and multiple queries well, and it supports nodes with several parents (a DAG). The cost is storage (O(n × depth) rows) and more work on insert and move, usually handled by triggers or application code.

Which should you use?

Need	Pick
General purpose, frequent edits	Adjacency list + recursive CTE
Read-heavy, path-like data (categories, URLs, files)	Materialized path (ltree, hierarchyid)
Fast ancestor and descendant queries, DAGs	Closure table
Huge, almost-never-changing tree	Nested sets

Start with the adjacency list. It's normalized, has no data to keep in sync, and with an index on parent_id a recursive CTE handles trees of tens of thousands of nodes in milliseconds. Add a materialized path or closure table next to it later if profiling shows subtree reads are the bottleneck.

Don't do this

• Fetching one level at a time in application code (a query per node). That's the N+1 problem with extra steps.
• A fixed number of columns (level1, level2, level3). Trees always grow one level deeper than you planned for.
• Storing children as a comma-separated list in the parent row. It can't be indexed, joined or constrained.

Checklist

1. Default to parent_id plus a recursive CTE, with an index on parent_id.
2. Protect recursive queries from cycles.
3. Use a materialized path for read-heavy, path-shaped hierarchies.
4. Use a closure table for fast queries in both directions or for DAGs.