Self joins: a table joined to itself

A self join joins a table to itself, under two different aliases. That lets a row be compared with another row of the same table, such as an employee with their manager.

Why it matters

manager_id points into the same employees table. To show "Hana reports to Ben", you need Hana's row and Ben's row side by side. There is only one table, so you use it twice.

The idea

Give the table two aliases and treat them as two copies: e for the employee, m for the manager. Join on m.emp_id = e.manager_id, meaning "the manager copy's id equals the employee copy's manager_id". Now each result row has the employee's columns and their manager's columns.

Use a LEFT JOIN if you want to keep Asha, who has no manager. Self joins also compare rows with each other — pairs in the same department, or rows on consecutive days.

Two photocopies of the staff list on your desk. On the left copy you find Hana and read "manager: 2". On the right copy you find person 2 — Ben — and write his name next to hers.

The photocopies are real copies. SQL doesn't copy anything: the aliases are just two names for the same table, which is why you must prefix every column (e.name, m.name) — otherwise "name" is ambiguous.

Syntax

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

How it works

  1. e and m are both the employees table.
  2. For Hana (manager_id 2), find the m row with emp_id 2: Ben.
  3. The result row holds e.name = Hana and m.name = Ben.
  4. Asha's manager_id is NULL, so she has no match; LEFT JOIN keeps her with a NULL manager.
  5. To compare rows, add a condition: a.emp_id < b.emp_id makes each pair appear once and stops a row pairing with itself.

Key points

Common mistakes

Not true: An INNER self join from employees to their managers also returns the person at the top.
In fact: The person at the top has no manager, so an INNER JOIN drops them. Use LEFT JOIN to keep every employee.

In the interview

A self join joins a table to itself with two aliases so I can compare rows within it — the classic case is employees and managers through a self-referencing manager_id. I join the manager alias's primary key to the employee alias's manager_id, and use a LEFT JOIN if the top of the hierarchy must appear. For pair comparisons I add id less-than id, to avoid self-pairs and mirrored duplicates.

Part of Unit 3: Joins: combining tables in the free SQL course.