A self-join joins a table to itself, using two aliases to represent two roles. It answers questions that compare rows within one table, such as who reports to whom, which rows share a value, or which events precede others.
Before you start
You should be comfortable with JOIN and aliases. This article uses PostgreSQL; the pattern is standard.
Step-by-step walkthrough
Step 1: Give each role an alias
Alias both references, for example employees e and employees m, so a column can be qualified by role. Without aliases the columns are ambiguous and the query will not parse. Read the aliases as the two roles the same rows play.
Step 2: Write the join condition that defines the relationship
The ON clause expresses the relationship, such as e.manager_id = m.id for a hierarchy or a.email = b.email AND a.id < b.id to pair duplicates without double-counting. The condition is what separates a self-join from a cross product.
Step 3: Choose the join type by intent
A LEFT JOIN keeps rows with no match, which is what you want for a hierarchy with a top-level node whose manager_id is null. An inner join drops those rows, which can silently hide them. Pick based on whether unmatched rows matter.
Worked scenario
The self-join pairs each employee with their manager, keeping top-level rows.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.name;Walk through the example
e is the employee role and m the manager role, joined on e.manager_id = m.id. The LEFT JOIN keeps employees whose manager_id is null, so the CEO appears with a NULL manager instead of being dropped. The ORDER BY makes the output stable for comparison.
Common mistake
Omitting the ON condition, which produces a cross product of every row with every other row, or forgetting the a.id < b.id guard when finding duplicate pairs, which returns each pair twice.
Verify the behavior
Assert that a top-level employee appears with a null manager under LEFT JOIN and is absent under inner join. For duplicate detection, confirm each pair appears once. Check the row count against the table size to catch an accidental cross product.
Interview exercise
How do you find pairs of customers that share the same email without listing each pair twice?
Answer and reasoning
Self-join on the email and add an ordering guard: FROM customers a JOIN customers b ON a.email = b.email AND a.id < b.id. The a.id < b.id condition keeps one ordering of each pair, so the result has no mirrored duplicates. Without it, every pair appears twice.
Continue learning
Compare join behavior in SQL join cardinality and exists versus join. Read the PostgreSQL joined tables documentation and try the SQL interview questions.