Self Join

Overview

A self join is a join where a table is joined to ITSELF. You write the same table name twice, giving each occurrence a different alias, then join on a relationship between rows in that table — typically a foreign key that points back to the same table's primary key. The classic example is the employees table, where manager_id references another row's id in the same table: SELECT e.name AS employee, m.name AS manager FROM employees e INNER JOIN employees m ON e.manager_id = m.id ORDER BY e.name; This pairs each employee with their manager. Sara (the CEO, manager_id = NULL) is excluded because INNER JOIN drops unmatched rows — she has no manager to match. Self joins are not a special syntax — they use the exact same JOIN you already know. The only difference is both sides of the join reference the same table, so aliases are mandatory to distinguish 'the employee row' from 'the manager row'. Mental model: imagine two COPIES of the same table side by side — one copy represents 'the child row', the other represents 'the parent row'. The ON clause links them.

Hierarchical data lives in a single table (employees → manager, categories → parent category, comments → parent comment). Self joins let you navigate one level of that hierarchy. They also let you compare rows within the same table — e.g., finding employees who earn more than their manager.

Where used: org chart and manager lookups, threaded comment trees, category → subcategory navigation, comparing rows in the same table

Why learn this

• Hierarchical relationships (employee→manager, category→parent) are stored in self-referencing tables — self join is the only way to query them
• Self joins unlock row-to-row comparisons within the same table, such as finding pairs of employees in the same department

Query walkthrough

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
ORDER BY e.name;

Focus: Watch the employees table appear TWICE — alias 'e' is the employee copy, alias 'm' is the manager copy. The ON clause links each employee's manager_id to the manager's id. Sara vanishes because her NULL manager_id matches no id.

Common mistakes

• Forgetting aliases: Both sides of the join reference the same table, so without aliases PostgreSQL cannot tell which 'name' or 'id' you mean. Always alias: FROM employees e JOIN employees m ON e.manager_id = m.id.
• Losing the root node with INNER JOIN: In the employees table Sara has manager_id = NULL. An INNER JOIN drops her because NULL never matches any id. Use LEFT JOIN if you want to include the root of the hierarchy (Sara appears with manager = NULL).
• Joining on the wrong direction: Writing ON e.id = m.manager_id reverses the direction — it pairs each employee with their SUBORDINATES, not their manager. Think carefully about which column is the foreign key and which is the primary key.

Glossary

occurrence

A specific instance or time that something appears, like writing the same table name twice in a query.

mandatory

Required by a rule or law; something that must be done, like using an alias when joining a table to itself.

subordinates

People or items that are at a lower rank or level in a hierarchy, like employees reporting to a manager.

Recall questions

• What makes a join a 'self join'?
• Why are aliases mandatory in a self join?
• What happens to Sara (manager_id = NULL) in an INNER self join on manager_id = id?

Understanding checks

What rows does this return? Pay attention to Sara.

Sara, NULL Tom, Sara Uma, Tom Victor, Tom Wendy, Sara Xavier, Wendy Yara, Wendy

LEFT JOIN keeps every employee, including Sara whose manager_id is NULL. Sara's manager column is NULL because she has no match. The other 6 employees are matched to their managers via manager_id = m.id.

Which employees earn more than their manager?

No rows are returned (empty result set).

Checking each employee against their manager: Tom(150000) vs Sara(200000) — no. Uma(120000) vs Tom(150000) — no. Victor(110000) vs Tom(150000) — no. Wendy(130000) vs Sara(200000) — no. Xavier(95000) vs Wendy(130000) — no. Yara(90000) vs Wendy(130000) — no. No employee earns more than their manager.

Practice tasks

Include the CEO in the results

This query uses INNER JOIN, so Sara (the CEO with no manager) is excluded. Change the join type so that Sara appears in the results with manager = NULL. Keep the ordering.

Challenge

Employees in the same department

Write a query that lists all pairs of employees who work in the same department. Show employee1 and employee2 names, and the department. Avoid duplicate pairs (ensure employee1 name < employee2 name). Order by department, then employee1.

Continue learning

Previous: INNER JOIN

Next: Recursive CTEs

Return to SQL Roadmap