SELF JOIN usage and examples

Overview

A self join gives one table two role aliases and connects it through parent-child keys or another relationship. Common uses include employee-manager relationships, category hierarchies, and adjacent records. Aliases should express roles, and a LEFT JOIN is required when root rows without a parent must remain visible.

Basic syntax

SELECT a.name, b.name FROM employees a JOIN employees b ON a.manager_id = b.id;

Examples

SELF JOIN example

The example treats employees as worker a and manager b, joining a.manager_id to b.id to return explicit reporting relationships.

Sample table

Table：employees

employees
id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Cindy	1
4	David	2

SQL

SELECT a.name AS employee, b.name AS manager FROM employees a JOIN employees b ON a.manager_id = b.id;

Run result

result
employee	manager
Bob	Alice
Cindy	Alice
David	Bob

Common issues

Why are aliases essential in a self join?

The same table plays different roles. Aliases distinguish the current row from its parent, predecessor, or match and prevent ambiguous columns.

How do I keep root rows with no parent?

Put the current row on the left and use LEFT JOIN. Parent-side columns are NULL for roots.

Should I chain self joins for unknown hierarchy depth?

Only for fixed depth. For unknown depth, use a recursive CTE with a termination condition and cycle protection.

Keep reading

INNER JOIN LEFT JOIN CROSS JOIN FULL OUTER JOIN

SELF JOIN