Self Join in SQL with Example
💡 Concept Name
Self Join is a regular join where a table is joined with itself to compare rows within the same table using aliases.
📘 Quick Intro
In SQL, a self join is used when you want to relate rows within the same table. A common use case is finding hierarchical relationships such as employees and their managers, both stored in a single Employee table.
🧠 Analogy / Short Story
Imagine a family tree where everyone is listed in one column, but we also want to know who their parent is—also listed in the same column. To match children with parents, you need to compare the table with itself, like looking at a mirror to identify relationships between faces. That’s what a self join does for data.
🔧 Technical Explanation
- A self join uses table aliases to distinguish between different instances of the same table.
- It is typically used with INNER JOIN or LEFT JOIN.
- Commonly used for hierarchical or recursive data—such as employees reporting to other employees.
- Requires a foreign key that relates to the same table's primary key.
🎯 Purpose & Use Case
- ✅ Find employees and their respective managers.
- ✅ Detect duplicate records within the same table.
- ✅ Match rows with similar data within the same table.
- ✅ Build hierarchies like category-subcategory, folder-subfolder relationships.
💻 Real Code Example
-- Employee table with ManagerID referring to another EmployeeID
SELECT e.EmployeeName AS Employee,
m.EmployeeName AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmployeeID;
❓ Interview Q&A
Q1: What is a self join?
A: A self join is a join where a table is joined with itself to compare rows within the same table using aliases.
Q2: When should you use a self join?
A: When rows in a table are related to other rows in the same table—such as employees and managers.
Q3: How do you distinguish between table instances in a self join?
A: By using aliases for the table, like `e` and `m` for the Employees table.
Q4: Can you use INNER JOIN in a self join?
A: Yes, INNER JOIN is often used when you only want matching rows from both instances of the table.
Q5: What happens if you forget to alias the table in a self join?
A: SQL throws an error due to ambiguity, as it cannot differentiate between the two instances of the same table.
Q6: Can a self join have a WHERE clause?
A: Yes, like any other join, it can be filtered with WHERE for specific conditions.
Q7: Is a self join limited to specific databases?
A: No, all major RDBMS like MySQL, SQL Server, PostgreSQL support self joins.
Q8: Can you self join a table more than once?
A: Yes, but you must use a unique alias each time.
Q9: Can self joins be used with aggregation?
A: Yes, for example to count how many subordinates a manager has.
Q10: How do self joins impact performance?
A: They can be slower on large tables if indexes aren’t optimized on the joined keys.
📝 MCQs
Q1. What is a self join?
- Join between two databases
- Join where table is joined with itself
- Join using UNION
- Recursive loop
Q2. Why use aliases in self joins?
- Shorten table names
- Enable ORDER BY
- To distinguish the same table used twice
- Run faster
Q3. Which clause is used in self join?
- MERGE
- TRIGGER
- JOIN with alias
- ROLLBACK
Q4. Which key relates to the same table in self join?
- Auto-increment key
- Group key
- Foreign key referencing primary key
- View key
Q5. Common use case of self join?
- User login
- Foreign table lookup
- Employee-manager hierarchy
- Storing large text
Q6. What if you omit alias in self join?
- Runs fine
- Joins all rows
- SQL throws ambiguity error
- Ignores second join
Q7. What type of join is often used in self join?
- RIGHT JOIN only
- FULL JOIN
- CROSS JOIN
- INNER or LEFT JOIN
Q8. Can self join be filtered?
- No
- Yes, using WHERE
- Only using HAVING
- Only on aggregate
Q9. Do all RDBMS support self joins?
- Only MySQL
- Only Oracle
- Yes
- No
Q10. Are self joins recursive?
- Yes
- No, but used for hierarchical data
- Only in triggers
- Depends on dialect
💡 Bonus Insight
You can perform multi-level hierarchies using recursive Common Table Expressions (CTEs) as an alternative to self joins. However, self joins are simpler and more readable for shallow relationships like employee-manager or category-subcategory links.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!