SQL Self join
Join a table to itself, with two aliases.
Syntax
FROM t AS a JOIN t AS b ON …
Used to compare rows of the same table: pairs, hierarchies, before/after.
Example
Query (on the shop database used in the SQL course)
SELECT a.name, b.name AS same_city_as, a.city FROM customers AS a JOIN customers AS b ON a.city = b.city AND a.customer_id < b.customer_id LIMIT 5;
Result
| name | same_city_as | city |
|---|---|---|
| Mia Chen | Bikash Thapa | Brisbane |
| Mia Chen | Ethan Nguyen | Brisbane |
| Mia Chen | Kiran Rai | Brisbane |
| Mia Chen | Noah Brown | Brisbane |
| Mia Chen | Sita Gurung | Brisbane |
5 rows
Related
Learn it step by step in the free lesson Joining Tables, with exercises that check your queries.