Self Join Example:
Users Table
| ID | USER_NAME | DEPARTMENT_NAME | MANAGER_ID |
|---|---|---|---|
| 1 | SYSTEM | IT | 1 |
| 2 | TOM | SALES | 2 |
| 3 | STEVE | SALES | 2 |
| 4 | ELLEN | SALES | 2 |
| 5 | JOHN | IT | 1 |
SELECT u.id, u.user_name, m.id manager_id, m.user_name manager
FROM users u, users m
WHERE u.manager_id = m.id
ORDER BY u.id ;
Result:
| Id | User_Name | Manager_Id | Manager |
|---|---|---|---|
| 1 | SYSTEM | 1 | SYSTEM |
| 2 | TOM | 2 | TOM |
| 3 | STEVE | 2 | TOM |
| 4 | ELLEN | 2 | TOM |
| 5 | JOHN | 1 | SYSTEM |