What is a Self Join in SQL?
Learn what a self join in SQL is, why it needs table aliases, and how to join a table to itself with employee-manager examples and interview tips.
Expected Interview Answer
A self join is a join in which a table is joined to itself, using table aliases so the same table can be referenced as two logical copies and rows can be compared against other rows in the same table.
Because SQL cannot reference one table twice without aliases, you give the table two aliases (for example e and m) and join them on a related column. Self joins are ideal for hierarchical or comparative data — such as employees and their managers stored in the same table via a manager_id column, or pairs of rows that share an attribute. Any join type (INNER, LEFT) can be a self join; the mechanics are identical to a normal join, only the two sides happen to be the same physical table.
- Models hierarchies (employee-manager, category-parent) in one table
- Compares rows within the same table without extra storage
- Finds pairs, duplicates or related records sharing a column
- Works with any join type and standard join syntax
- Avoids duplicating data into a second lookup table
AI Mentor Explanation
Think of a team sheet where each player also lists a senior player who mentors them. To pair every junior with their mentor you read the same team sheet twice at once — once looking for the junior, once looking up the mentor's row by ID. That single roster, cross-referenced against itself, is exactly a self join matching rows to other rows in the same list.
Step-by-Step Explanation
Step 1
Choose the table
Pick the single table that contains a column referring to another row in the same table, such as employees with a manager_id.
Step 2
Assign two aliases
Reference the table twice with distinct aliases (e.g. e for employee, m for manager) so SQL can treat them as separate logical copies.
Step 3
Define the join condition
Join on the linking columns, for example e.manager_id = m.id, to connect each row to its related row.
Step 4
Pick the join type
Use INNER JOIN to keep only matched pairs, or LEFT JOIN to keep rows (like top-level managers) that have no match.
Step 5
Select qualified columns
Always prefix columns with the alias (e.name, m.name) to disambiguate identically named columns from the two copies.
What Interviewer Expects
- Understanding that the same physical table is referenced twice via aliases
- Correct use of aliases to disambiguate columns
- A concrete hierarchical example like employee-manager
- Awareness that any join type can be a self join
- Knowing when a self join is appropriate versus a subquery
Common Mistakes
- Forgetting to alias the table, causing ambiguous column errors
- Using INNER JOIN and silently dropping top-level rows with no parent
- Confusing a self join with a cross join or cartesian product
- Not qualifying columns with aliases in the SELECT list
- Assuming a self join needs a second copy of the data stored on disk
Best Answer (HR Friendly)
“A self join is when you join a table to itself to compare rows within the same table. A common example is an employees table where each person's manager is also an employee in the same table, so you join it to itself to list each employee next to their manager.”
Code Example
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id
ORDER BY manager, employee;Follow-up Questions
- Why do you need table aliases in a self join?
- How would you list employees who have no manager?
- How does a LEFT self join differ from an INNER self join here?
- Can a self join be rewritten as a correlated subquery?
- How would you find all pairs of employees in the same department?
MCQ Practice
1. What is a self join?
A self join references the same table twice via aliases so rows can be compared against other rows in the same table.
2. Why are aliases required in a self join?
Without distinct aliases SQL cannot tell which copy of the table a column belongs to, causing ambiguous column errors.
3. To keep top-level managers who have no manager_id, which join should you use?
A LEFT JOIN keeps every employee row even when there is no matching manager row, preserving rows with a NULL manager_id.
Flash Cards
What is a self join? — A join where a table is joined to itself using aliases to compare rows within the same table.
Why use aliases in a self join? — To distinguish the two logical copies of the same table and avoid ambiguous column references.
Classic self join example? — An employees table joined to itself on e.manager_id = m.id to pair each employee with their manager.
INNER vs LEFT self join? — INNER keeps only matched pairs; LEFT also keeps rows with no matching parent, such as top-level managers.