Self joins: a table joined to itself

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  subgraph E["employees as e"]
    H["Hana · manager_id 2"]
    B1["Ben · manager_id 1"]
  end
  subgraph M["employees as m"]
    B2["2 · Ben"]
    A["1 · Asha"]
  end
  H -->|"m.emp_id = e.manager_id"| B2
  B1 --> A

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

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

SELECT a.name AS first, b.name AS second, a.dept_id
FROM employees a
JOIN employees b ON b.dept_id = a.dept_id AND a.emp_id < b.emp_id
ORDER BY a.dept_id, a.name, b.name;

SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
GROUP BY m.emp_id, m.name
ORDER BY direct_reports DESC, manager;

Must-know points

• A self join uses the same table twice with two aliases.
• Read the ON condition carefully: m.emp_id = e.manager_id means "m is e's manager".
• Use LEFT JOIN to keep rows whose pointer is NULL (the top of the tree).
• a.id < b.id gives each pair once; a.id <> b.id gives each pair twice.
• A self join reaches one level. For a whole chain of managers, use a recursive CTE (Phase 4).

Common mistakes

• Swapping the condition (e.emp_id = m.manager_id), which lists each person's reports instead of their manager.
• Forgetting prefixes, so name is ambiguous.
• Using INNER JOIN and losing the CEO.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL Self Join

  short page with a try-it editor, free

• Practice

  SQLZoo — Self join

  free exercises

• Practice

  PostgreSQL Exercises — Joins and Subqueries

  pgexercises.com, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what m.emp_id = e.manager_id means in plain words.

   Show answer

   Read the employees table twice, as two copies: e for the employee, m for the manager. m.emp_id = e.manager_id says: "for this employee e, find the row in m whose id is the number written in e's manager_id".

   For Hana, manager_id is 2, so the match is the row with emp_id 2: Ben. So m.name is her manager's name.

2. Writefrom a blank page, then run it

   Write a query showing each employee, their manager, and their manager's manager (two self joins).

   Show answer

   Join once to get the manager (m), then join again from the manager to their manager (mm). Use LEFT JOINs so people at the top still show, with NULLs: Asha has no manager, and the people under Asha have no manager's manager.

   sqlCopy

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

   Try it

   8 rows

   employee	manager	managers_manager
   Asha	NULL	NULL
   Ben	Asha	NULL
   Chen	Asha	NULL
   Dara	Asha	NULL
   Eli	Dara	Asha
   Fatima	Asha	NULL
   Gus	Dara	Asha
   Hana	Ben	Asha

3. Predictsay it before you run it

   How many rows does the first example return if LEFT JOIN becomes JOIN? Who is missing?

   Show answer

   7 rows instead of 8. Asha is missing: her manager_id is NULL, so she has no matching manager row, and an INNER JOIN drops her.

   sqlCopy

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

   Try it

   7 rows

   employee	manager
   Ben	Asha
   Chen	Asha
   Dara	Asha
   Eli	Dara
   Fatima	Asha
   Gus	Dara
   Hana	Ben

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Find employees hired before their manager. Who has no manager, and what does your query do with them?

   Show answer

   Join each employee to their manager, then compare the two hire dates: WHERE e.hire_date < m.hire_date.

   In this data the answer is nobody: no rows. Every manager joined before the people under them. (Asha joined in 2019, Dara and Ben in 2020, and everyone they manage later.) An empty result is a real answer; don't "fix" the query until it returns something.

   Asha has no manager. The INNER JOIN drops her before the WHERE runs. With a LEFT JOIN she would reach the WHERE, but e.hire_date < NULL is unknown, so she would be dropped anyway. Either way, she is never counted as "hired before her manager", which is right.

   sqlCopy

   SELECT e.name, e.hire_date, m.name AS manager, m.hire_date AS manager_hired
   FROM employees e
   JOIN employees m ON m.emp_id = e.manager_id
   WHERE e.hire_date < m.hire_date
   ORDER BY e.name;

   Try it

   No rows

   name	hire_date	manager	manager_hired

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, use a LEFT JOIN if the top of the hierarchy must appear, and for pair comparisons I add id less-than id to avoid self-pairs and mirrored duplicates.

Follow-up questions

Tick each check when you can do it without notes