What is a self JOIN and when do you need it?
SQLLevel: FoundationJOINs Basics
What is a self JOIN and when do you need it?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
A self JOIN joins a table to itself. You need table aliases to distinguish the two references. Classic use case: an employee table where each employee has a manager_id that references another row in the same table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Other use cases: finding pairs of rows that share some attribute, finding related items in a hierarchy.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT