Introduction
A self join is simply a regular join (INNER, LEFT, etc.) where a table is joined to itself. It is used when rows in a single table have a relationship to other rows in that same table, such as an employees table where each row has a manager_id that points to another employee's employee_id, or an orders table where you want to compare each customer's orders against each other. Because both sides of the join come from the same table, you must use two different aliases to distinguish them.
Cricket analogy: A self join is like a team roster table joined to itself to compare each player against their designated mentor, both drawn from the same players table using two different aliases to tell them apart.
Syntax
-- customers table extended with a referred_by column
-- customer_id | customer_name | referred_by
SELECT a.customer_name AS customer,
b.customer_name AS referred_by_customer
FROM customers AS a
LEFT JOIN customers AS b
ON a.referred_by = b.customer_id;Explanation
The FROM clause references customers twice under two aliases, a and b, so the engine treats them as two independent copies of the table for join purposes. The ON condition links a row in one copy to a related row in the other copy — here, a.referred_by (a foreign key pointing to another customer) is matched against b.customer_id. A LEFT JOIN is often used for self joins on optional relationships (like referred_by, which may be NULL) so that customers with no referrer are still included, with referred_by_customer showing NULL. An INNER JOIN self join would exclude customers with no referrer entirely.
Cricket analogy: Aliasing customers as a and b is like treating two copies of the same scorecard as separate sheets to compare a batter's current form against their form last season, matching referred_by to customer_id across the two copies.
Example
-- customers
-- customer_id | customer_name | referred_by
-- 1 | Alice | NULL
-- 2 | Bob | 1
-- 3 | Carla | 1
-- 4 | Dev | 2
SELECT a.customer_name AS customer,
b.customer_name AS referred_by_customer
FROM customers a
LEFT JOIN customers b ON a.referred_by = b.customer_id;Output
Result rows: Alice/NULL (no referrer), Bob/Alice, Carla/Alice, Dev/Bob. Alice's referred_by_customer is NULL because her referred_by column is NULL and the LEFT JOIN preserves her row anyway with the right side NULL-padded. Bob and Carla both resolve to Alice since referred_by = 1 matches customer_id 1. Dev resolves to Bob since referred_by = 2 matches customer_id 2.
Cricket analogy: Alice with NULL referrer, then Bob and Carla both linked to Alice, and Dev linked to Bob, is like a scorer tracing a fielding drill where Alice trained herself, then trained Bob and Carla, and Bob later trained Dev.
Key Takeaways
- A self join joins a table to itself, requiring two distinct aliases to reference each 'copy' separately.
- Common use cases: hierarchical data (employee-manager), finding duplicates, comparing rows pairwise (e.g., products in the same category).
- LEFT JOIN self joins preserve rows whose self-referencing key is NULL or has no match.
- INNER JOIN self joins exclude rows without a valid self-reference match.
- Self joins are still just INNER/LEFT/RIGHT/FULL joins — the 'self' aspect is only about the table being reused, not a new join type.
Practice what you learned
1. What is required to write a self join in SQL?
2. Which classic scenario is best solved with a self join?
3. In a LEFT JOIN self join on customers.referred_by = customers.customer_id, what happens to a customer with referred_by = NULL?
4. If you used INNER JOIN instead of LEFT JOIN for the referral self join, what would happen to customers with no referrer?
Was this page helpful?
You May Also Like
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.
LEFT and RIGHT JOIN
Preserve all rows from one table, NULL-padding columns from the other table when there is no match.
Correlated Subqueries
A subquery that references columns from the outer query and is re-evaluated once for every row processed by the outer query.