SQL Self Join
intermediatejoins
2 min read
What is a Self Join?
A self join joins a table to itself. This is useful when rows in a table have relationships with other rows in the same table, such as employees and their managers.
Syntax
SELECT a.column, b.column
FROM table_name a
INNER JOIN table_name b ON a.related_id = b.id;When to Use
- Employee-manager hierarchies
- Comparing rows within the same table
- Finding pairs or duplicates
Key Points
- Aliases Required — You must alias the table with two different names.
- Any Join Type — You can use INNER JOIN, LEFT JOIN, or any other join type.
- Hierarchies — Self joins are the classic way to query parent-child relationships.
- Performance — Self joins on large tables should have indexed join columns.
- Recursive CTEs — For deep hierarchies, consider recursive CTEs as an alternative.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL Self Join Challenge
Write a query that solve this task: show each employee alongside their manager name.
Expected result
Each employee with their manager name. Top-level employees show NULL for manager.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Self Join?
Initializing database...Each run starts from fresh sample data.
More Examples
Find students in the same grade
Pair students who share the same grade.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Why do I need aliases for a self join?
The database needs to distinguish between the two references to the same table. Aliases let you refer to each instance separately.
Can I use LEFT JOIN for a self join?
Yes. A LEFT self join ensures all rows from the first reference appear, even if there is no matching row in the second.