Joins, CTEs & Recursion

SQL Basics covered SELECT against a single table. Almost nothing interesting stays in one table for long — joins combine rows across tables, GROUP BY/HAVING collapse them into summaries, and CTEs (WITH ...) let you name intermediate results instead of nesting subqueries five levels deep.

Join types

Sample schema for this lesson: employees(id, name, manager_id, department_id) and departments(id, name).

-- only rows that match on both sides
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- every employee, department NULL if unassigned
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- every department, employee NULL if empty
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- everything from both sides, NULLs where either side is missing
SELECT e.name, d.name AS department
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

INNER JOIN is the default JOIN — rows drop out entirely if either side has no match. LEFT JOIN (and its mirror, RIGHT JOIN) keeps every row from the "kept" side and fills unmatched columns with NULL. FULL JOIN keeps everything from both sides. In practice RIGHT JOIN is rare — it's almost always written as a LEFT JOIN with the tables swapped instead, since that reads more naturally left-to-right.

GROUP BY and HAVING

GROUP BY collapses matching rows into one row per group; HAVING filters groups the same way WHERE filters rows, but it runs after aggregation, so it can reference aggregate functions where WHERE can't:

SELECT department_id, count(*) AS headcount
FROM employees
GROUP BY department_id
HAVING count(*) > 3
ORDER BY headcount DESC;

WHERE filters rows before grouping happens; HAVING filters the groups produced by that grouping. WHERE count(*) > 3 is a syntax error for exactly this reason — at the point WHERE runs, count(*) doesn't exist yet.

CTEs: naming intermediate results

A CTE (WITH ... AS (...)) gives a subquery a name you can reference later in the statement, one or more times:

WITH dept_counts AS (
  SELECT department_id, count(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT d.name, dc.headcount
FROM dept_counts dc
JOIN departments d ON d.id = dc.department_id
ORDER BY dc.headcount DESC;

This is purely a readability tool for non-recursive CTEs — every one of them can be rewritten as a nested subquery. The value is that a chain of WITH clauses reads top-to-bottom like a sequence of steps, instead of nesting inside-out.

Recursive CTEs: walking a tree

WITH RECURSIVE is the standard way to walk hierarchical data — org charts, category trees, bill-of-materials — in plain SQL, without a procedural loop. It has two parts joined by UNION ALL: an anchor that seeds the starting rows, and a recursive term that references the CTE's own name and is re-executed against only the rows produced by the previous iteration, until it returns nothing.

WITH RECURSIVE org_chart AS (
  -- anchor: top of the tree
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive term: one level down each pass
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT repeat('  ', depth - 1) || name AS org_tree, depth
FROM org_chart
ORDER BY depth, name;

Each pass only sees the rows the previous pass added — not the whole accumulated result — which is what makes this a proper breadth-first traversal and not an infinite self-join. UNION ALL (not UNION) is deliberate: deduplicating would require comparing every new row against everything accumulated so far, which is both slower and wrong if you genuinely expect duplicate rows (e.g. the same employee ID reachable via two manager paths in a non-tree graph).

Next, a different way to keep every row instead of collapsing them into groups: Window Functions.