Skip to content
Quantoros

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

Related questions