Lesson 5.4

SQL Self-Joins

A self-join lists one table under two aliases so you can compare its rows to each other.

A self-join is an ordinary join where both sides are thesame table. Nothing special happens in the engine. The only trick is that you must give each copy its own alias, like c1 and c2, so the query can tell the two rows apart.

Self-joins answer questions that compare rows within one table: pairs of products in the same category, an employee and their manager, or customers who live in the same city.

Pattern

select a.id, b.id
from things as a
join things as b
  on a.group_id = b.group_id
  and a.id < b.id

Guard against self-pairs and duplicates

If you join on the shared attribute alone, every row also matchesitself, and each real pair shows up twice: once as (A, B) and again as (B, A). The fix is a single condition, a.id < b.id, which keeps one ordered copy of each pair and removes the self-matches.

Try the starter query first without that guard. You will see a customer paired with themselves and each city pair listed twice. Then add and c1.customer_id < c2.customer_id and watch the noise disappear.

Schema · Garden ShopTable · customers9 columns · 20 rows

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id intfirst_name textlast_name textemail textphone textcity textstate textsignup_date dateis_active bool

Your task

Find every pair of customers who live in the same city. Return the city and both customers' full names, and make sure no customer is paired with themselves and no pair is listed twice.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer