**Basic Hierarchy Query:**

```sql

WITH RECURSIVE hierarchy AS (

-- Base case: root nodes (no parent)

SELECT

id,

name,

parent_id,

1 as level, -- Start at level 1

name as path -- Initialize path

FROM table_name

WHERE parent_id IS NULL -- Root condition

UNION ALL

-- Recursive case: find children

SELECT

child.id,

child.name,

child.parent_id,

parent.level + 1, -- Increment level

parent.path || ' → ' || child.name -- Append to path

FROM table_name child

JOIN hierarchy parent ON child.parent_id = parent.id

WHERE parent.level < 10 -- Prevent infinite recursion

)

SELECT * FROM hierarchy

ORDER BY level, name;

```

**Component Breakdown:**

- **WITH RECURSIVE**: Declares recursive CTE

- **Base case**: WHERE parent_id IS NULL finds roots

- **UNION ALL**: Combines base and recursive results

- **Recursive case**: JOIN finds children of previous iteration

- **level + 1**: Tracks depth in hierarchy

- **path concatenation**: Builds full path from root to node

- **WHERE level < 10**: Safety limit to prevent runaway queries

Base case must select root nodes (WHERE parent_id IS NULL)

Recursive case joins table to CTE on parent-child relationship

UNION ALL combines base and recursive results (never UNION)

Add level counter starting at 1, increment in recursive part

Build paths by concatenating names with separator (||)