Module 2: Joining Tables ⏱ 18 min

Self Joins and Hierarchical Data

By the end of this lesson you will be able to:
  • Join a table to itself by aliasing it twice
  • Use an inequality guard so each pair appears exactly once
  • Compare rows within one table, such as coworkers or peers

Comparing rows to other rows in the same table

Some questions set a table against itself. "Who shares a department with whom?" needs two employees at once. "Which projects sit in the same department?" needs two projects. A normal join reaches for a second table, but here both halves of the answer live in employees — there is no second table to name.

The trick is to pretend there is. You list the same table twice in the FROM clause and give each copy a different alias. The database treats the two copies as independent, and a join between them compares every row to every other row. It feels odd the first time, then obvious: a self-join is just a join where the two tables happen to be one.

flowchart LR
  T[("employees")] --> A["alias a"]
  T --> B["alias b"]
  A --> J["match:<br/>a.department_id<br/>= b.department_id"]
  B --> J
  J --> R["paired rows"]
  style T fill:#1e293b,color:#fff
  style J fill:#0e7490,color:#fff
  style R fill:#0e7490,color:#fff
One physical table, two aliased copies — the join pairs rows of copy a against rows of copy b.

Two aliases, one table

The alias is what makes a self-join legal. Without distinct names, SQL cannot tell the two copies apart, so every column reference must carry its alias — a.name versus b.name — or you hit an "ambiguous column name" error at once.

SELECT a.name AS person, b.name AS coworker
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id;

The ON links a row of copy a to a row of copy b when their departments match. Run this as written and you will spot a problem immediately, which is exactly the trap worth meeting on purpose.

Run this unguarded version first — notice every person paired with themselves, and every real pair listed twice.
SELECT a.name AS person, b.name AS coworker
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id;

The guard that keeps each pair once

The unguarded query suffers two ailments. It pairs each employee with themselves, because Ada's department equals Ada's department. And it lists every genuine pair twice — (Ada, Bo) and (Bo, Ada) — because the symmetry goes both ways.

The cure is one extra condition in the ON: insist on an ordering between the two ids. Adding AND a.id < b.id keeps only the half where a's id is smaller, which drops the self-pairs (an id is never less than itself) and collapses each mirror to a single row. Note that a.id <> b.id would fix the self-pairs but leave the doubling intact, so reach for < (or >) when you want each unordered pair exactly once.

flowchart TD
  A["pair a.id=1, b.id=2"] --> K["kept: 1 is less than 2"]
  B["pair a.id=2, b.id=1"] --> D["dropped: mirror"]
  C["pair a.id=1, b.id=1"] --> S["dropped: self-pair"]
  style K fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff
  style S fill:#b45309,color:#fff
The inequality guard keeps one direction of each pair and drops self-pairs and mirrors.
Guarded: each coworker pair appears once, lower id first.
SELECT a.name AS person, b.name AS coworker
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id AND a.id < b.id;

Compound conditions compare any two rows

Once the aliases exist, the ON can compare anything about the two rows, not just the linking key. Pair employees in the same department where one out-earns the other by writing AND a.salary < b.salary. Each conjunct narrows which rows count as a pair, exactly as AND narrows a WHERE.

This is the real payoff of self-joins: ranking, duplicate-finding, and "who is unlike me" questions all reduce to comparing a row to its siblings. The linking condition stays the same; only the comparison changes.

Each employee beside a same-department coworker who earns more.
SELECT a.name, a.salary, b.name AS earns_more, b.salary
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id AND a.salary < b.salary
ORDER BY a.name;

Qualify, always

With one table playing two roles, an unqualified column is meaningless to the engine. SELECT name fails because name exists on both a and b; a.name and b.name name them precisely. This is the one rule a self-join will not forgive, so write the alias on every column from the first keystroke. Aliases like a and b are fine for a demo; in real code, reach for meaningful names like emp and mgr so the comparison reads back clearly months later.

Exercise

List each pair of employees who are in the same department. Show them as person and coworker, each unordered pair exactly once with the lower id first. Alias the table a and b.

SELECT a.name AS person, b.name AS coworker
FROM employees a
-- join employees to itself and guard the pairing here
;
Exercise

For each employee, find a coworker in the same department who earns more than them. Return the employee's name and label the other earns_more.

SELECT a.name, b.name AS earns_more
FROM employees a
-- self-join and compare salaries here
;
Exercise

This self-join pairs employees in the same department, but it has no guard — it lists every employee with themselves and shows each real pair twice. Add the condition so each distinct pair appears exactly once, lower id first.

SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.department_id = b.department_id
;
Exercise

Transfer the technique to another table. List each pair of projects that belong to the same department, each pair once with the lower id first. Show both project names.

SELECT a.name, b.name
FROM projects a
-- self-join projects and guard the pairing here
;
Exercise

Find pairs of employees in the same department who hold the same role (for example, two Engineers). Show both names and the shared role, each pair once with the lower id first.

SELECT a.name, b.name, a.role
FROM employees a
-- self-join, match department and role, then guard here
;

Recap

  • A self-join lists one table twice under different aliases to compare its rows.
  • Qualify every column (a.name, b.name) — an unqualified name is ambiguous.
  • Guard pairings with AND a.id < b.id so each unordered pair appears once and self-pairs disappear.
  • The ON can compare any columns (salary, role, dates), not just the linking key.
  • The textbook case is a manager_id self-reference; our seed uses coworkers, same mechanics.

Next you will meet the join that pairs every row with every row — CROSS JOIN — and the accident that produces one when you never meant to.

Checkpoint quiz

Why must you alias a table in a self-join?

What does adding AND a.id < b.id to a self-join's ON accomplish?

Go deeper — technical resources