What is Self Join?
Employees Table
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
3 rows
Self Join joins a table to itself. Sounds confusing, but it is actually simple!
Simple analogy: Employee table where each employee has a manager (who is also an employee).
Basic Example
Employees Table
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
3 rows
Employees Table:
| id | name | manager_id | |----|-------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Carol | 1 |
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Result:
- Alice - NULL (no manager)
- Bob - Alice (manager is Alice)
- Carol - Alice (manager is Alice)
When to Use
Employees Table
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
3 rows
Use when data in one table relates to other data in the same table.
Examples:
- Employees and their managers
- Products and related products
- Categories and parent categories
What Comes Next
Employees Table
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
3 rows
Next: Cross Join (combines every row with every row)