Working with Joins
1. Using INNER JOIN
SELECT o.id, c.full_name, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id;| Result | Detail |
|---|---|
| Rows | Matching pairs only |
| Synonym | JOIN = INNER JOIN |
2. Using LEFT JOIN
SELECT c.id, c.full_name, count(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;| Behavior | Detail |
|---|---|
| Preserves | All rows from left side |
| Unmatched right | NULL columns |
3. Using RIGHT JOIN
SELECT o.id, c.full_name
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;Note: Equivalent to swapping operand order with LEFT JOIN; LEFT is more idiomatic.
4. Using FULL OUTER JOIN
SELECT a.id, b.id
FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key;| Returns | Detail |
|---|---|
| All rows | From both sides |
| Unmatched | NULL for missing side |
5. Using CROSS JOIN
SELECT s.name, c.code FROM sizes s CROSS JOIN colors c;
-- Equivalent: SELECT ... FROM sizes s, colors c;6. Using Self Join
SELECT e.id, e.name, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;7. Using NATURAL JOIN
SELECT * FROM orders NATURAL JOIN customers;Warning: NATURAL JOIN auto-matches any columns with the same name. Avoid—silently breaks when schemas evolve.
8. Using JOIN with ON Clause
SELECT * FROM a JOIN b ON a.x = b.y AND a.tenant = b.tenant;| Predicate | Goes In |
|---|---|
| Join condition | ON clause |
| Row filter | WHERE (post-join) |
| Filter on right side of LEFT JOIN | ON to preserve outer rows |
9. Using JOIN with USING Clause
SELECT * FROM orders JOIN customers USING (customer_id);Note: Both columns must be named identically; result has only one merged column.
10. Chaining Multiple Joins
SELECT o.id, c.full_name, p.name, 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;11. Understanding Join Order and Performance
| Method | Best For |
|---|---|
| Nested Loop | Small outer + indexed inner |
| Hash Join | Large unsorted inputs, equi-join |
| Merge Join | Sorted inputs (large equi-join) |
Note: Planner re-orders joins; force order with
join_collapse_limit = 1 or CTE materialization.12. Using JOIN with WHERE Clause
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US' AND o.placed_at >= '2026-01-01';