AdvancedJOINs (INNER, LEFT, RIGHT, FULL)

Hierarchical query: find all subordinates of a manager

The query

SQL

WITH RECURSIVE subordinates AS (
  -- Start with the target manager
  SELECT emp_id, first_name, last_name, manager_id, 0 AS level
  FROM employees
  WHERE emp_id = 5 -- Target manager ID

  UNION ALL

  -- Find direct reports recursively
  SELECT e.emp_id, e.first_name, e.last_name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT
  REPEAT('  ', level) || first_name || ' ' || last_name AS name,
  level AS depth
FROM subordinates
ORDER BY level, last_name;

Tested against PostgreSQL 16

Note

“Recursive query to get all people under a manager in a hierarchy. Classic org-chart query.

Tables referenced

employees10 cols