SELF JOIN: Join a Table with Itself
A self join is a join where a table is joined with itself.
It is used when rows in the same table are related to each other, like an employee and their manager.
To tell the two copies of the table apart, you give each one a table alias (a short name).
Syntax
SELECT a.column, b.column
FROM table_name AS a
JOIN table_name AS b
ON a.common_column = b.related_column;
Example Scenario
For this lesson, we use a table called staff. Each person has a manager_id, which is the emp_id of their manager:
SELECT * FROM staff;
Output:
emp_id | name | manager_id
--------+---------+------------
1 | Alice |
2 | Bob | 1
3 | Charlie | 1
4 | David | 2
5 | Emma | 2
(5 rows)
Here:
- Alice is the top manager. Nobody manages her, so her
manager_idisNULL. - Bob and Charlie report to Alice (
manager_id1). - David and Emma report to Bob (
manager_id2).
Example Query
List every person with their manager's name:
SELECT e.name AS employee, m.name AS manager
FROM staff AS e
LEFT JOIN staff AS m
ON e.manager_id = m.emp_id
ORDER BY e.emp_id;
Output:
employee | manager
----------+---------
Alice |
Bob | Alice
Charlie | Alice
David | Bob
Emma | Bob
(5 rows)
- The
stafftable is used twice: once ase(the employee) and once asm(the manager). - We used
LEFT JOINso that Alice is still in the result. She has no manager, somanagerisNULL.
When to Use a Self Join?
When a table has rows that point to other rows in the same table, for example:
- Employees and their managers
- Categories and their parent categories
- People and the people who invited them
In the exercise, join the staff table with itself to pair managers with their team.