Self Joins
Join a table to itself
A self join is when a table is joined to itself. This is useful for hierarchical or comparative data within the same table.
Example: Employee Hierarchy
employees
| id | name | manager_id |
|---|---|---|
| 1 | Sarah (CEO) | NULL |
| 2 | John | 1 |
| 3 | Jane | 1 |
| 4 | Bob | 2 |
Finding Managers
-- Get each employee with their manager's name
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Result:
-- Sarah | NULL (CEO has no manager)
-- John | Sarah
-- Jane | Sarah
-- Bob | JohnFinding Direct Reports
-- Count direct reports for each manager
SELECT
m.name AS manager,
COUNT(e.id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name
HAVING COUNT(e.id) > 0;
-- Result:
-- Sarah | 2
-- John | 1Comparing Within Table
-- Find products more expensive than another product
SELECT
p1.name AS product,
p1.price,
p2.name AS cheaper_than
FROM products p1
JOIN products p2 ON p1.price > p2.price
WHERE p2.name = 'Basic Plan';Important: Always use different aliases (e, m) when self-joining to distinguish between the two "copies" of the table.