Self-Joins in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this INNER JOIN, Column Aliases and Expression Naming
Builds toward Correlated Subqueries
What are Self-Joins in SQL?
A self-join joins a table to itself, using two separate aliases to treat it as two distinct copies.
You're looking at an org chart. The employees table has a manager_id column that points to another row in the same table — each employee's row stores the ID of their manager, who is also an employee. To get an employee's name alongside their manager's name, both pieces of data live in the same table. You can't pull from two different tables, because there's only one table. You pull from the same table twice, with different aliases.
How do you write a self-join in SQL?
The syntax looks exactly like a regular join. The only difference is that FROM and JOIN reference the same table name, and you must give each copy a distinct alias:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.ide is the employee copy. m is the manager copy. SQL scans e for each employee row, then looks up the matching row in m where m.id equals that employee's manager_id. The result: employee name next to manager name on the same row.
Why does a self-join drop the top of an org chart?
Because this is an INNER JOIN, employees with no manager_id — typically executives at the top of the hierarchy — are excluded. They have no matching row in the manager copy. To include them, use LEFT JOIN:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.idEmployees with no manager get NULL in manager_name. Executives included.
Self-joins work with any table that has rows relating to other rows in the same table. Categories with a parent_id, products that reference a base product, org charts, threaded comments:
SELECT e.name AS employee_name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.department_id = 1
The same filtering and ordering rules apply as any other join.
Why does a self-join need two table aliases?
The one thing that trips people up: forgetting that each copy of the table needs a distinct alias.
If you write FROM employees JOIN employees ON ... without aliases, SQL raises an error because it can't tell which copy you're referencing in the SELECT list or ON clause. Pick two short, meaningful aliases — e and m for employee and manager, c and p for child and parent — and use them consistently throughout the query.
Practice Self-Joins in SQL
Helix Systems' HR team is building an employee directory and needs each person listed alongside their direct manager.
Write a query to return the employee name and manager name for every employee who has a manager on record.
Assumptions:
- The
employeestable contains every active and former employee at Helix Systems. - The
manager_idcolumn points to another row inemployees— the employee's direct manager. - Top-of-hierarchy executives have a missing
manager_idand will not appear in the result.
Output:
- One row per employee with a manager, with columns
employee_nameandmanager_name.
Schema · hr4 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Self-Joins practice problems
Write a query to return the employee name and manager name for every employee who has a manager on record.
Write a query to return the subcategory name and parent category name for every subcategory that belongs to another category.
Write a query to return each employee's name alongside their manager's name and the manager's job title.
Write a query to return the employee name and manager name for every Engineering team member who has a manager on record.
Write a query to return the subcategory name and parent category name for every subcategory whose parent is 'Clothing'.
Write a query to return all three values for every employee with a manager on record.
Write a query to return the employee name and manager name for every such employee.
Write a query to return the name of each category in every sibling pair.
Write a query to return the employee's name, their direct manager's name, and their manager's manager's name for every employee who has both levels available.
Start learning to practice all 9 Self-Joins problems, with instant grading and mastery tracking.
Common questions about Self-Joins
What happens if you forget the aliases in a self-join?
The query stops, saying the table name was specified more than once. Both halves of the join are the same table, so without two distinct aliases there is no way to say which copy a column belongs to, and PostgreSQL refuses rather than guessing.
Can you join a table to itself more than once?
Yes. Each copy needs its own alias, and then a third can be chained onto the second, which is how you reach an employee, their manager and their manager in turn. Every extra copy narrows the result to rows where the whole chain exists.
Does a self-join have to be an INNER JOIN?
No, and often it should not be. An inner self-join silently drops the row at the top of the hierarchy, because nobody is above it to match. Switch to a LEFT JOIN when the root belongs in the answer.
