Advanced joins

01The problem

HR asks for a staff list showing each person's manager. The employees table has a manager_id column, but it holds a number, not a name, and the manager is just another row in the same table.

The same week, sales asks, "Which customers have ever used air freight?" A plain join returns one row per air shipment, so a customer with 30 air shipments appears 30 times. You could add DISTINCT, but that hides the real question, which is about existence, not about combining rows.

Joins you already know (INNER, LEFT) combine matching rows. This lesson covers the joins that answer other kinds of questions.

02The concept

A semi-join never duplicates rows, however many matches there are, and it stops looking at the first match.

Recursive CTEs have two parts joined by UNION ALL: an anchor (the starting rows, such as people with no manager) and a recursive part that joins the CTE to the table to find the next level down. It repeats until a level finds no new rows. Always carry a level column, and make sure the recursion can end: a loop in the data (A manages B, B manages A) would run forever, and a WHERE level < 10 guard prevents that.

03Example

The staff list with managers, via a self join. The same table appears twice, with different aliases:

SELECT
  e.employee_id,
  e.full_name,
  e.role,
  COALESCE(m.full_name, '(no manager)') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;

LEFT JOIN keeps the two team leads, who have no manager. An inner join would silently drop the most senior people in the list.

Now the whole reporting tree, with a recursive CTE:

WITH RECURSIVE org AS (
  SELECT employee_id, full_name, role, 0 AS level, full_name AS path
  FROM employees
  WHERE manager_id IS NULL                      -- anchor: the top of the tree
  UNION ALL
  SELECT e.employee_id, e.full_name, e.role, o.level + 1, o.path || ' > ' || e.full_name
  FROM employees AS e
  JOIN org AS o ON e.manager_id = o.employee_id -- the next level down
  WHERE o.level < 10                            -- guard against loops
)
SELECT level, path, role
FROM org
ORDER BY path;

Harbourline's tree is only two levels deep, but the same query works unchanged for a company with ten.

04Walkthrough

1. Run the self join. Change LEFT JOIN to JOIN and count the rows: 22, not 24.
2. Run the recursive CTE, then remove WHERE manager_id IS NULL from the anchor. Every employee becomes a starting point and people appear several times. The anchor decides where the tree starts.
3. Try a range join. Group shipments into size bands defined in a small inline table:

WITH bands(band, lo, hi) AS (
  VALUES ('Small (1-2)', 1, 2), ('Medium (3-5)', 3, 5), ('Large (6-8)', 6, 8)
)
SELECT b.band, COUNT(*) AS shipments, ROUND(AVG(s.freight_charge)) AS avg_charge
FROM shipments AS s
JOIN bands AS b ON s.containers BETWEEN b.lo AND b.hi
GROUP BY b.band, b.lo
ORDER BY b.lo;

4. Note that the bands live in data, not in a long CASE. When finance changes the bands, you change three rows, not the query.

05Practice

06Challenge

07More practice

08Check your understanding