What is a self join and when do you need one?
A self join joins a table to itself using two different aliases. It is used when rows relate to other rows in the same table, such as an employee and their manager.
Explain it simply
In a college, every student record may have a "class representative" who is also a student. The same list holds both the person and their representative. To show each name next to its representative, you look at the list twice: once as "student", once as "representative". That is a self join.
Example
Table employees(id, name, manager_id):
| id | name | manager_id |
|---|---|---|
| 1 | Kavita | NULL |
| 2 | Rohan | 1 |
| 3 | Priya | 1 |
| 4 | Aman | 2 |
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Result:
| employee | manager |
|---|---|
| Kavita | NULL |
| Rohan | Kavita |
| Priya | Kavita |
| Aman | Rohan |
LEFT JOIN keeps Kavita, who has no manager. An INNER JOIN would drop her.
What to say in the interview
"A self join treats one table as two by giving it two aliases. It fits hierarchies like employee-manager, or comparing rows within a table, such as pairs of flights on the same route. I use LEFT JOIN when the top of the hierarchy must stay in the result."
Common mistakes
- Not aliasing, which makes column names ambiguous.
- Using INNER JOIN and losing the root row.