What is Self Join?
Self Join joins a table to itself. Used when rows in a table relate to other rows in the same table.
Simple rule: Same table, two aliases (e, m)
How Self Join Works
Basic Syntax
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Key: Use aliases e and m for same table!
Example: Find Each Employee's Manager
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Result:
- Alice - NULL (boss, no manager)
- Bob - Alice
- Carol - Alice
When to Use
- Employees → Managers
- Categories → Parent Categories
- Products → Related Products
Try It Below
Practice with employee-manager data!
What Comes Next
Next: Cross Join - every row with every row!
Self Join Result
Employee-Manager relationships. Alice is boss (manager='None').
Source Table: students
| employee | manager |
|---|---|
| Alice | None |
| Bob | Alice |
| Carol | Alice |
| David | Bob |
4 rows
SQL Editor
Loading...
Find Alice's Team
Who reports to Alice? Try: SELECT employee FROM joined_data;
Source Table: students
| employee | manager |
|---|---|
| Alice | None |
| Bob | Alice |
| Carol | Alice |
| David | Bob |
4 rows
SQL Editor
Loading...