SQL Self Join
A self join uses one table in two roles. An employee directory is a common example: an employee row points to another employee row that represents the manager. Aliases keep the roles separate.
Understand the idea
The staff table has a nullable manager_id. We use a left join because the top manager has no manager of her own. employee and manager are two references to the same stored table, not separate copies of it. This reads one level of the hierarchy; a recursive CTE can follow several levels.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT employee.name AS employee, manager.name AS manager
FROM staff AS employee
LEFT JOIN staff AS manager ON manager.staff_id = employee.manager_id
ORDER BY employee.staff_id;
Read the result
Leena has no manager, Ravi reports to Leena, and Isha reports to Ravi. The NULL beside Leena describes a missing relationship rather than an error.
Try one change
Show only staff who have a manager using an inner join. Explain why Leena disappears.