♻️ SQL SELF JOIN – Compare Rows in the Same Table
🧲 Introduction – What is a SELF JOIN?
A SELF JOIN is a regular join where a table is joined to itself to compare rows within the same table. This is useful for hierarchical relationships, comparisons, or data dependencies inside one table.
🎯 In this guide, you’ll learn:
- Syntax and use cases of
SELF JOIN - How to use aliases to distinguish joined instances
- Real-world examples such as employee-manager relationships
✅ 1. SELF JOIN Syntax
SELECT A.column1, B.column2
FROM table_name A
JOIN table_name B ON A.common_field = B.common_field;
✅ You must use table aliases to differentiate the two instances.
👥 2. Example – Employees and Their Managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
✅ Matches employees with their corresponding managers.
🧩 3. Example – Compare Values in the Same Table
SELECT A.name AS item1, B.name AS item2
FROM products A
JOIN products B ON A.category = B.category AND A.price < B.price;
✅ Finds products in the same category where one is cheaper than the other.
🔄 4. SELF JOIN with NULLs (Outer Self Join)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
✅ Includes employees who do not have a manager (e.g., CEO).
📘 Best Practices
| ✅ Recommended | ❌ Avoid This |
|---|---|
| Use short aliases for clarity | Confusing table names (e.g., t1, t2) |
| Comment your join purpose | Leaving logic unexplained |
| Consider LEFT JOIN to preserve all rows | INNER JOIN hides unmatched rows |
📌 Summary – Recap & Next Steps
A SELF JOIN enables row-to-row comparisons within a single table. It’s ideal for building hierarchies, peer relationships, or tiered logic.
🔍 Key Takeaways:
- Use aliases (
A,B) to distinguish the table instances - JOIN on related columns inside the same table
- Use LEFT JOIN to include unmatched rows (optional heads or roots)
⚙️ Real-World Relevance:
Used in HR (managerial reporting), bill of materials, dependency tracking, and friendship networks.
➡️ Next: Learn about recursive CTEs for deeper hierarchical querying.
❓ FAQ – SQL SELF JOIN
❓ What is a SELF JOIN in SQL?
✅ A join that connects a table to itself using aliases.
❓ When should I use a SELF JOIN?
✅ When you want to compare or relate rows within the same table (e.g., employee-manager).
❓ Do I need aliases for SELF JOIN?
✅ Yes. They’re required to distinguish the two roles of the same table.
❓ Can I use LEFT JOIN in SELF JOIN?
✅ Yes. To include rows that don’t match, like managers without supervisors.
Share Now :
