BeginnerCoder
DonateLesson 21 — Self join: finding pairs
Self join for pairs
Lesson 15 joined each row to one related row (its manager). A self join can also build pairs: every combination of two rows that share something. Joining products to themselves ON the same category gives all pairs — but also each product with itself, and every pair twice (A–B and B–A). The fix is a.id < b.id: each pair appears once, and never a product with itself.
FROM players x JOIN players y
ON x.club = y.club AND x.id < y.id
Why it matters: "items often compared", "people in the same city", "possible duplicate records" — pair-finding is a daily data-cleaning job.
Extend the ON condition so each pair appears only once and no product is paired with itself. Expected: exactly 4 unique pairs, no self-pairs.
Run your code — when the output matches the task, you will see a green check. Then mark the lesson complete.
Test your knowledgeA quick 3-question quiz on this lesson — instant feedback.
Ready for more? Optional paid resources that pick up where this lesson leaves off.
Some links on this page are affiliate links: we may earn a commission at no extra cost to you. As an Amazon Associate I earn from qualifying purchases.
Project-based SQL Deep Dive courses if you want a structured paid path after the free lessons. Wait for a sale — prices drop often.
Browse on Udemy →Search Amazon for SQL Deep Dive books — check the publication year so the examples match current versions.
Search Amazon →