Self join

Join a table to itself to read a relationship that lives inside one table.

7 min read

Overview

A self join joins a table to itself. You reach for it when the rows you need to connect already live in the same table, linked by a column that points from one row to another. The employees table is the standard case. The smaller monthly_revenue table supports a neighboring-row pattern later in the Guide. Each employee row carries an id, a name, and the manager’s id. A department and a salary complete the row.

employees

id	name	manager_id	dept	salary
1	Devin	null	Exec	320
2	Maya	1	Sales	150
3	Omar	1	Sales	140
4	Priya	2	Eng	160

monthly_revenue (contiguous numeric month ordinal)

month	revenue
1	100
2	140
3	120

Look at the manager_id column, where Maya’s value is 1 and row 1 belongs to Devin. Her manager is not stored in some other table. The value simply points at another row of the same table. To put every employee next to their manager, you read employees twice and line the two readings up.

How it works

A self join is an ordinary join in which the same table supplies both inputs. In this example, the employee-side alias e names one reading of employees. Each e row is the employee row whose manager_id is being read. The manager-side alias m names a second reading of employees. Each m row is a possible manager whose id can satisfy that reference. The predicate e.manager_id = m.id matches the manager identifier from an employee row to the identifier from a manager row. The aliases let you write e.name and m.name without asking the database to guess which reading of employees you mean.

The join type determines how an employee with no matching manager appears. An inner join keeps only e rows that find an m match. A left join keeps every e row and supplies null manager columns where m has no match. Predict which row pairs survive the self-join before playing the demo. Then write the employee-manager pairs produced by e.manager_id = m.id, say which table read e and m name, and decide whether an inner join or a left join retains Devin.

One table, read as twoPress play, or step through it yourself.

select e.name as employee, m.name as manager
from employees e
left join employees m on e.manager_id = m.id;

employees e

id	name	manager_id
1	Devin	null
2	Maya	1
3	Omar	1
4	Priya	2

employees m

id	name
1	Devin
2	Maya
3	Omar
4	Priya

result

empmgr

e.manager_id finds m.id

Under the inner join Devin disappears because manager_id is null and matches no manager row. Under the left join his employee row survives and the manager columns become null. The prediction is complete when it names the surviving pairs and explains the alias relationship.

Patterns

The mechanism stays the same in every use. First name the two roles that the table plays, then write the condition that connects them. The first and third patterns reuse employees. The monthly-revenue pattern uses the compact source table above.

1
Find pairs in the same table
To pair two different employees in the same department, alias one read a and the other b, then join both reads of employees on a.dept = b.dept. The condition a.id < b.id does two jobs. It stops a row from pairing with itself, and it returns each pair once instead of both Maya-Omar and Omar-Maya.
```
select a.name, b.name, a.dept
from employees a
join employees b
  on a.dept = b.dept
 and a.id < b.id;
```
a b dept
Maya Omar Sales
2
Compare a row to its neighbor
The monthly_revenue table above has one row for each contiguous numeric month. Its values 1, 2, and 3 describe neighboring periods. To compare each month with the one before it, join the table to itself where one month is one less than the other. A left join keeps the first month, which has nothing before it, with a null change. The subtraction is valid for this ordinal only. Calendar dates, YYYYMM values, and sparse months need a different adjacency rule.
```
select cur.month, cur.revenue,
       cur.revenue - prev.revenue as change
from monthly_revenue cur
left join monthly_revenue prev
  on prev.month = cur.month - 1;
```
month rev change
1 100 null
2 140 +40
3 120 -20
3
Match on a comparison, not equality
A match does not have to be equality. To count colleagues who earn more than each employee, use e for the employee being counted and h for a higher-paid colleague. Join employees e to employees h on h.salary > e.salary, then count the matching h rows for each e row. This can be expensive because it intentionally creates many pairs.

a	b	dept
Maya	Omar	Sales

month	rev	change
1	100	null
2	140	+40
3	120	-20

Trade-offs

For calculations across rows in an ordered series, continue with Window functions. That Guide owns the analytical vocabulary and method selection. This Guide stays focused on relationships expressed by two roles of one table.

Pitfalls

The row that quietly vanishes

An inner join drops every row whose pointer is null. Here that is Devin, the person at the top. The query still returns rows and looks correct, while the person at the top has silently disappeared from the answer. This is the most common self-join bug, so reach for a left join whenever the whole set matters.

It reaches one level. A self join gives you a person and their manager, not their manager’s manager. A hierarchy of unknown depth needs a recursive CTE.
Drop the condition and you get a cross join. With no on clause, every row pairs with every row. Ten thousand rows become a hundred million.
Name collisions need an alias. Reference a shared column without one and the database stops with an ambiguous column error. It cannot know which copy you meant.

Performance

The condition determines whether a self join behaves like a key lookup or creates many pairs. Estimate result cardinality before running comparison-based shapes such as a.id < b.id or h.salary > e.salary.

Practice

EasyBuild an employee-manager roster with two aliases and a left self join. Every employee must appear once, with the top manager retained under a null manager.MediumProduce same-department employee pairs under a.id < b.id. The output may contain no self-pairs or reverse duplicates, and no pair may cross departments.HardImplement month-over-month change with a left self join over a contiguous numeric month ordinal. Keep the first row with a null change and return the later deltas. Then explain why subtracting one does not work for calendar dates, YYYYMM, or sparse months.

Recap

Use a self join when two related roles or rows live in the same table, then alias each role.
Select inner or left deliberately based on whether a null pointer must survive.
Order a pair predicate such as a.id < b.id to remove self-pairs and reverse duplicates.
A recursive CTE fits when the relationship can span an unknown number of levels.
Window functions are a separate ordered-series topic. Continue with Window functions when you are ready to study it.
Estimate the pair count before running comparison conditions on a large table.