SQL Patterns 7–9: Self-Join, Cross Join, Market Basket
💡 Insight
Join-based patterns. The key insight: SQL can join a table to ITSELF.
Once you understand this, a whole category of questions becomes easy.
🧠 MEMORY MAP
🧠 JOIN PATTERNS = "SCM"
JOIN PATTERNS"SCM"
SSelf-Join: same table twice (hierarchy, pairs, comparisons)
CCross Join: every row × every row (combinations, grids)
MMarket Basket: self-join on order_id (items bought together)
SELF-JOIN KEY RULE:
Use TWO aliases for the same table: FROM employees e1 JOIN employees e2
The JOIN condition defines the RELATIONSHIP between the two copies:
→ e2.manager_id = e1.id (hierarchy)
→ e1.score - e2.score <= 5 (similarity)
→ e1.item_id < e2.item_id (pairs, avoid duplicates)
CROSS JOIN RULE
N rows × M rows = N×M rows (Cartesian product)
Use only when you WANT every combination (grids, matchups, combinations)
Never accidentally CROSS JOIN large tables → explodes data!
MARKET BASKET KEY RULE
Self-join order_items ON same order_id BUT different product
Use: o1.product_id < o2.product_id→prevents (A,B) AND (B,A) both appearing
PATTERN 7: SELF-JOIN
What Is It?
Join a table to itself to compare rows within the same table, traverse hierarchies, or find relationships between records of the same type.
Recognize It When You See:
- "Employee and their manager" (same employees table)
- "Find pairs that share [something]"
- "Users who both attended [same event]"
- "Salary higher than [their own manager]"
- "Students who scored within N points of each other"
- "Players who beat [same opponent] twice"
THE TEMPLATE
sql
-- TEMPLATE: Self-Join (two aliases for same table)
-- Pattern A: Hierarchy (employee → manager)
SELECT
e.name AS employee_name,
e.salary AS employee_salary,
m.name AS manager_name,
m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id -- join condition defines relations