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 :
