SELF JOIN
Join a table to itself — for hierarchies and row comparisons
Intermediate 12 minSELF JOIN
Joining a Table to Itself
A self join uses the same table on both sides of the JOIN with different aliases to distinguish the two references. The database doesn't actually copy the table — it just references it twice. Here's when you need it:
Rows that reference other rows — The classic example: an employees table where each row has a manager_id pointing to another employee. Self joins also work for finding products in the same category or customers in the same country.
Use a.id < b.id for pairs — When generating pairs, this filter prevents both duplicate pairs (A-B and B-A) and self-pairs (A-A). Without it, the result contains redundant combinations that double or triple your expected row count.
Different aliases are required —
FROM products a JOIN products b ON a.category = b.category — without distinct aliases like a and b, SQL cannot tell which table reference you mean in the ON and SELECT clauses.Knowledge Canvas
How SELF JOIN Works
Join a table to itself
- Same table referenced twice with different aliases
- Enables row-to-row comparisons within one table
- Uses INNER, LEFT, or CROSS JOIN syntax
- Essential for hierarchical data (employee → manager)
When to Use Self Join
Classic scenarios
- Employee → manager hierarchies
- Find pairs: products in same category
- Compare sequential rows (before window functions)
- Find duplicates with different IDs
Self Join Traps
Common issues
- Without
a.id < b.id, you get duplicate pairs AND self-pairs - Aliases are REQUIRED — can't reference same table name twice
- Cartesian explosion if ON condition is too loose
- Recursive hierarchies need CTEs, not simple self joins
Dedup Pattern
Avoid duplicate pairs
WHERE a.id < b.idprevents (A,B) and (B,A) duplicates- Also prevents self-pairs like (A,A)
- Use
<for unordered pairs,!=for directed pairs - Count pairs:
SELECT COUNT(*) / 2if using!=
Syntax
Syntax Template
1SELECT
2 a.col,
3 b.col
4FROM table_name a
5JOIN table_name b
6 ON a.some_col = b.some_col
7 AND a.id < b.id;
a, bDifferent aliases for the same tablea.id < b.idPrevents duplicate and self-pairs Sample Data
customers
12 rows
| id | name | country |
|---|---|---|
| C1001 | Aarav Sharma | India |
| C1002 | Sara Chen | Singapore |
| C1003 | James Wilson | UK |
| C1004 | Maria Garcia | Spain |
| C1005 | Yuki Tanaka | Japan |
| C1006 | Priya Patel | India |
| C1007 | Alex Johnson | USA |
| C1008 | Chen Wei | China |
| C1009 | Emma Brown | Australia |
| C1010 | Omar Hassan | UAE |
| C1011 | Lena Muller | Germany |
| C1012 | Ravi Kumar | India |
products
10 rows
| id | name | category | price | stock |
|---|---|---|---|---|
| P101 | Wireless Mouse | Electronics | 24.99 | 150 |
| P102 | Office Desk | Furniture | 189 | 25 |
| P103 | LED Desk Lamp | Furniture | 45.5 | 80 |
| P104 | USB-C Cable | Electronics | 12.99 | 500 |
| P105 | Bluetooth Speaker | Electronics | 79 | 60 |
| P106 | Office Chair | Furniture | 95 | 15 |
| P107 | Coffee Maker | Appliances | 89.99 | 40 |
| P108 | Notebook Set | Stationery | 8.5 | 200 |
| P109 | Wireless Headphones | Electronics | 159 | 30 |
| P110 | Standing Desk | Furniture | 425 | 8 |
Worked Example
Find pairs of customers who live in the same country.
SQL
1SELECT
2 a.name AS customer_1,
3 b.name AS customer_2,
4 a.country
5FROM customers a
6JOIN customers b
7 ON a.country = b.country
8 AND a.id < b.id;
We join customers to itself matching on country. The a.id < b.id condition ensures each pair appears only once (Aarav-Priya, not also Priya-Aarav) and no one is paired with themselves.
Output
3 rows
| customer_1 | customer_2 | country |
|---|---|---|
| Aarav Sharma | Priya Patel | India |
| Aarav Sharma | Ravi Kumar | India |
| Priya Patel | Ravi Kumar | India |
Key Concepts
1Same table, two different aliases — the database references it twice
2Use a.id < b.id to prevent duplicate and self-pairs
3Essential for hierarchies: employee → manager relationships
4Self joins work with INNER, LEFT, or CROSS JOIN syntax
Pro Tip
Hierarchical data (org charts) and comparative analysis (find similar items) both require self joins.
When to Use
Org charts (employee → manager), finding duplicate records, comparing rows within the same table, matching pairs.
Find pairs of products in the same category. Show product_1, product_2, and category. Only show each pair once.
Your Query