Working with SQL Joins
1. Understanding Join Types
| Join | Returns |
|---|---|
| INNER JOIN | Rows matching in both tables |
| LEFT [OUTER] JOIN | All left rows + matched right (NULL if none) |
| RIGHT [OUTER] JOIN | All right rows + matched left |
| FULL [OUTER] JOIN | All rows from both, NULLs where unmatched |
| CROSS JOIN | Cartesian product (m × n) |
| SELF JOIN | Table joined to itself |
| LATERAL (PG) | Right side may reference left columns |
| SEMI JOIN | EXISTS-style, returns left rows that match |
| ANTI JOIN | NOT EXISTS-style, left rows without match |
2. Using INNER JOIN
Example: INNER JOIN
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= CURRENT_DATE - INTERVAL '7 days';| Property | Behavior |
|---|---|
| Default | JOIN alone = INNER JOIN |
| USING(col) | Same column name on both sides, collapses |
| NATURAL JOIN | Joins on all matching column names (fragile, avoid) |
3. Using LEFT JOIN
Example: Find Customers With No Orders
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;| Use Case | Pattern |
|---|---|
| Optional related rows | Show parent even without children |
| Anti-join | LEFT JOIN + WHERE right_col IS NULL |
| Aggregates | Preserve zero-count parents |
4. Using RIGHT JOIN
| Note | Detail |
|---|---|
| Equivalent to LEFT JOIN | Swap table order → use LEFT for readability |
| Rarely seen | Most teams standardize on LEFT JOIN |
5. Using FULL OUTER JOIN
Example: Reconciliation Across Two Sources
SELECT COALESCE(a.id, b.id) AS id, a.amount AS src_a, b.amount AS src_b
FROM ledger_a a
FULL OUTER JOIN ledger_b b ON a.id = b.id
WHERE a.amount IS DISTINCT FROM b.amount;| Use | Detail |
|---|---|
| Diff datasets | Find rows present in one but not the other |
| MySQL | No native FULL — emulate with LEFT UNION RIGHT |
6. Creating CROSS JOIN
| Use | Example |
|---|---|
| Generate combinations | days × products for sparse fact filling |
| Pair with LATERAL | Per-row computed subqueries |
| Risk | Accidental cross join from missing ON → row explosion |
7. Performing Self Joins
Example: Employee with Manager Name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;| Use | Example |
|---|---|
| Hierarchy | employee → manager |
| Comparisons | Find pairs of rows (e.g., duplicate detection) |
| Sequencing | Compare row to previous/next via join on offset |
8. Using Multiple Joins
Example: 3-Table Join
SELECT o.id, c.name, p.name AS product, oi.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.placed_at >= NOW() - INTERVAL '30 days';| Tip | Detail |
|---|---|
| Filter early | Push selective predicates to reduce join inputs |
| Index FK columns | Both sides should have indexes for hash/merge plans |
| Watch row multiplication | Joining two M:N can explode → use DISTINCT or aggregate |
9. Optimizing Join Performance
| Strategy | Detail |
|---|---|
| Nested Loop | Best for small inner table + index on join key |
| Hash Join | Build hash on smaller side; great for large equi-joins |
| Merge Join | Both inputs sorted on join key — large datasets |
| Statistics | Run ANALYZE; stale stats → bad plans |
| Join order | Most planners explore orderings; force with hints sparingly |
10. Understanding Join Order and Execution Plans
| Tool | Command |
|---|---|
| Postgres | EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ... |
| MySQL | EXPLAIN FORMAT=TREE / EXPLAIN ANALYZE |
| SQL Server | SET STATISTICS PROFILE ON / Actual Execution Plan |
| Oracle | EXPLAIN PLAN FOR ... ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
Plan example:
HashJoin cost=... rows=...
HashCond: (oi.order_id = o.id)
-> Seq Scan on order_items oi
-> Hash
-> Index Scan on orders o
Index Cond: (placed_at >= '2026-04-20')