Writing the Self-Join Condition

Concepts covered: Self-Join, Outer Joins, Nulls in Joins, Null Handling

The join condition is the sentence that says how a row in one role finds its partner in the other. For an org chart the pointer is manager_id: it sits on the employee's row and holds the employee_id of the manager's row. So the condition is e.manager_id = m.employee_id, read as 'the employee's manager_id equals the manager's employee_id'. Every self-join over a self-referencing column has this shape: the foreign key on the alias playing the child role, the primary key on the alias playing the parent role.

Which side holds the pointer

Before typing the ON clause, answer one question: which column is the pointer, and which row does it live on? In employees, the pointer is manager_id and it lives on every row that has a boss. The alias whose manager_id you read is therefore the employee, and the alias whose employee_id you compare it to is the manager. Carol's row has manager_id 1; the m row with employee_id 1 is Alice; the condition pairs Carol with Alice. Trace exactly one row like this out loud and the direction is settled before any syntax exists.

The reversed condition

The mistake that costs the most here is writing the condition backwards: e.employee_id = m.manager_id. It compiles and returns rows, but they are the wrong rows, and the column labels misstate what they hold.

-- Reversed: pairs each person with the people who report to them
SELECT
  e.name AS employee_name,
  m.name AS manager_name
FROM employees e
JOIN employees m
  ON e.employee_id = m.manager_id;

The output says Alice's manager is Bob, and also Carol, which is nonsense, since Alice has no manager at all. What the reversed condition computed is 'for each person e, every m whose manager_id points back at e', which is the list of each person's direct reports with the labels swapped. The row count is 3 in both the correct and the reversed version, so counting rows will not catch it. The only check that works is picking one output row and asking whether it is true: 'is Bob Alice's manager?' No. Swap the sides.

The row with nothing to match

Alice's manager_id is NULL. When the join compares her row against each of the 4 m rows, NULL = 1 comes back unknown rather than true. The same happens with NULL = 2 and NULL = 3, and again with NULL = 4, so an inner join finds no partner and drops her. For 'who earns more than their manager' that is the correct behaviour: the CEO has no manager to compare against. For 'show me every employee with their manager's name' it is a bug, and the fix is LEFT JOIN, which keeps every e row and fills the m columns with NULL where no partner exists.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id

Watch Alice's row in the run. Each of her 4 comparisons resolves to unknown, no m row attaches, and the LEFT JOIN emits her with manager NULL. The 3 other employees each pick up the same single manager they got from the inner join in section 1, so switching to LEFT JOIN changes only what happens to the row that finds nothing.

A pointer that lands nowhere fails the match the same way. If Bob's row were deleted while Dave's manager_id still said 2, the inner join would drop Dave, and the LEFT JOIN would emit him with NULL in every m column, exactly as it emits Alice. The m columns look alike; e.manager_id does not. Alice's is NULL because she has no manager, and Dave's is still 2 because his manager's row is gone. Filtering on both columns after the LEFT JOIN separates them, and it is the query to have ready when the interviewer asks about deleted managers.

-- Employees whose manager_id points at no existing row
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.employee_id
WHERE e.manager_id IS NOT NULL
  AND m.employee_id IS NULL;

About this lesson

This section is part of the Self-Join: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.