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';

Related Cheat Sheets

Postgresql