Home / SQL / Reference / Joins

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

namesame_city_ascity
Mia ChenBikash ThapaBrisbane
Mia ChenEthan NguyenBrisbane
Mia ChenKiran RaiBrisbane
Mia ChenNoah BrownBrisbane
Mia ChenSita GurungBrisbane

5 rows

Related

Learn it step by step in the free lesson Joining Tables, with exercises that check your queries.