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 the same 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 matches itself, 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. The || in the starter glues text together to build each full name; Lesson 7.3 covers it.

Preparing for an interview? The self-join interview questions put this pattern under pressure, with model answers to compare against.

Syntax reference

Need syntax details? See the SQL JOIN types reference

Schema · Garden Shop Table · customers 9 columns · 20 rows

Table · customers

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

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_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.