Detailed explanation
A SELF JOIN joins a table to itself so that rows in that table can be compared or related to other rows in the same table. It is not a separate SQL join operator: you use a normal JOIN, LEFT JOIN, or another supported join type, referencing the same table with different aliases. Each alias represents a distinct role in the query. This is useful when related entities share one table, such as employees and their managers, categories and their parent categories, or users who referred other users.
In the example, SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id, the alias e represents an employee and m represents that employee’s manager. The join condition matches the manager_id value in the employee row with the id value in another row of employees. If Asha has manager_id = 10 and Ravi has id = 10, the result contains Asha as the employee and Ravi as the manager. Qualifying columns with aliases makes these roles explicit and avoids ambiguity.
Because the example uses an INNER JOIN, it returns only employees with a matching manager row. Employees whose manager_id is NULL, such as a top-level manager, are excluded, as are employees whose manager_id has no matching id. To retain every employee, use FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; missing manager details then appear as NULL. Logically, SQL treats the aliases as separate inputs, but this does not require physically copying the table. The optimiser chooses an execution strategy based on indexes, statistics, and the database engine.
Interviewers usually check whether you can explain the aliases, write the correct relationship condition, and choose the appropriate join type. A single self join follows one relationship level; additional joins can follow a fixed number of levels, while a recursive CTE is typically used for variable-depth hierarchy traversal where supported. Self joins also support comparisons between peers, such as finding employee pairs in the same department. In those cases, conditions such as e1.id < e2.id prevent self-pairs and reversed duplicates.
Key points to remember
- A self join references the same table more than once using distinct aliases.
- It uses ordinary join operators; SELF JOIN is not a separate SQL keyword.
- The condition e.manager_id = m.id connects each employee to their manager.
- INNER JOIN excludes unmatched employees; LEFT JOIN preserves them with NULL manager columns.
- One self join follows one hierarchy level; recursive CTEs support variable-depth traversal.
Common mistakes candidates make
- Joining e.id to m.id when the requirement is to link employees to managers.
- Using INNER JOIN while expecting employees with no matching manager to appear.
- Generating peer pairs without excluding self-pairs or reversed duplicates.
Likely follow-up questions
How would you include employees who have no manager?
Use SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. Every employee remains in the result, and manager is NULL when there is no matching manager row.
Can a self join retrieve an employee’s entire management chain?
One self join retrieves only the immediate manager. You can add joins for a known number of levels, but a recursive CTE is generally better for variable-depth chains and should account for possible cycles.
How can you find unique employee pairs in the same department?
Use SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id < e2.id. Assuming id is unique, the inequality excludes self-pairs and returns each matching pair only once.
Interview tip: Explain the two alias roles, walk through one employee–manager match, and then contrast INNER JOIN with LEFT JOIN.