--- title: "SQL Joins Cheatsheet" slug: "sql-joins" category: "cheatsheets" tags: ["cheatsheets", "sql", "postgres", "joins", "database", "reference"] status: "stable" last_updated: 2026-05-14 summary: "SQL join types with set-diagram semantics, syntax, and the NULL and row-multiplication bugs that bite most often." related: [ "[[backend/postgres]]", "[[backend/postgres-indexes]]", "[[backend/postgres-explain]]", "[[cheatsheets/postgres-explain]]", "[[cheatsheets/postgres-functions]]", "[[cheatsheets/postgres-types]]", "[[comparisons/postgres-vs-mysql]]", "[[cheatsheets/index|Cheatsheets]]", ] --- ## Overview A join combines rows from two relations based on a condition. The join type determines what happens when no match exists. Choosing the wrong type produces silently wrong result counts; understanding NULL propagation and Cartesian-product risks prevents the most common mistakes. Examples use Postgres syntax throughout; behavior is identical in MySQL, SQLite, and most other ANSI SQL engines except where noted. For index strategy behind join performance, see [[backend/postgres-indexes]]. ## Join types Pick the join that reflects which rows are required from each side. | Join type | Rows returned | Typical use | | ----------------- | -------------------------------------------------------------- | ------------------------------------------------------------- | | `INNER JOIN` | Only rows with a match on both sides. | Require both entities to exist. | | `LEFT JOIN` | All rows from left; `NULL` for unmatched right. | Optional right-side relationship. | | `RIGHT JOIN` | All rows from right; `NULL` for unmatched left. | Rarely preferred; rewrite as `LEFT JOIN` with tables swapped. | | `FULL OUTER JOIN` | All rows from both sides; `NULL` for missing side. | Reconcile two data sources with partial overlap. | | `CROSS JOIN` | Cartesian product; every left row paired with every right row. | Combination generation; no `ON` clause. | | `SELF JOIN` | Table joined to itself via aliases. | Hierarchy traversal, adjacency lists, finding duplicates. | | `LATERAL JOIN` | Right side is a subquery that references each left row. | Per-row subqueries, `LIMIT` per group. | ```sql -- INNER JOIN SELECT o.id, u.name FROM orders o JOIN users u ON u.id = o.user_id; -- LEFT JOIN: users with or without orders SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; -- FULL OUTER JOIN: find rows missing on either side SELECT a.key, b.key FROM source_a a FULL OUTER JOIN source_b b ON a.key = b.key WHERE a.key IS NULL OR b.key IS NULL; -- CROSS JOIN: all size/color combinations SELECT s.name AS size, c.name AS color FROM sizes s CROSS JOIN colors c; ``` ## Set diagram reference Think in terms of which rows each join keeps. | Diagram | Pattern | What to write | | ----------------- | ----------------------------------- | --------------------------------------------------------------- | | Intersection only | Both sides match | `INNER JOIN ... ON ...` | | Left circle only | Left side, no match on right | `LEFT JOIN ... WHERE right.id IS NULL` | | Right circle only | Right side, no match on left | `RIGHT JOIN ... WHERE left.id IS NULL` | | Left circle full | All left, matched right is optional | `LEFT JOIN` | | Right circle full | All right, matched left is optional | `RIGHT JOIN` (or swap to `LEFT JOIN`) | | Full union | All rows from both sides | `FULL OUTER JOIN` | | Outer ring only | Rows not in the intersection | `FULL OUTER JOIN ... WHERE left.id IS NULL OR right.id IS NULL` | ## Self join and lateral These patterns do not fit neatly in the diagram. ```sql -- Self join: employee and their manager SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id; -- LATERAL: top 3 orders per customer (Postgres-specific) SELECT u.name, recent.created_at FROM users u CROSS JOIN LATERAL ( SELECT created_at FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3 ) recent; ``` `LATERAL` allows the subquery to reference columns from the outer `FROM` clause row. Without `LATERAL`, the subquery is independent. ## Join conditions and performance The `ON` clause drives both correctness and index usage. | Practice | Reason | | ------------------------------------------------------------- | --------------------------------------------------------------------------------------------- | | Put the foreign key on the smaller table side. | The planner uses the index on the join key of the larger table. | | Avoid functions on join columns: `ON f(a.id) = b.id`. | Functions prevent index use; move the transformation to the query setup. | | Use `USING (col)` when both sides share the same column name. | Cleaner syntax; the column appears once in `SELECT *`. | | Prefer `JOIN` over comma-separated tables. | Comma syntax is a CROSS JOIN; missing `WHERE` conditions produce Cartesian products silently. | ## Common bugs The mistakes that produce wrong row counts without errors. | Bug | Symptom | Fix | | -------------------------------------- | -------------------------------------------------------------------- | ------------------------------------------------------------------------------------ | | NULL comparison with `=` | `WHERE a.id = NULL` never matches. | Use `IS NULL` / `IS NOT NULL`. | | Filtering a LEFT JOIN in WHERE | `WHERE right.col = 'x'` turns the LEFT JOIN into an INNER JOIN. | Move the filter into the `ON` clause. | | One-to-many join without aggregation | Result has more rows than expected; aggregates are inflated. | Add `GROUP BY` or use a subquery to aggregate first. | | CROSS JOIN without `ON` | Row count = left rows × right rows; memory blows up on large tables. | Always add an `ON` clause; use `CROSS JOIN` only when Cartesian product is intended. | | Duplicate column names with `SELECT *` | Column names collide when both tables share a name. | Alias tables and list columns explicitly. | | Missing index on join key | Seq scan on large tables; slow queries. | Add a B-tree index on the foreign key column. | ```sql -- Bug: filtering NULL-side of LEFT JOIN in WHERE converts to INNER JOIN -- Wrong SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'; -- drops users with no orders -- Correct: filter in ON clause SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'; ``` ## Common gotchas - `RIGHT JOIN` is logically equivalent to `LEFT JOIN` with the table order swapped. Prefer `LEFT JOIN` for consistency; mixing both styles in one query is confusing. - `FULL OUTER JOIN` is not available in MySQL 5.x. Emulate with `UNION` of a `LEFT JOIN` and a `RIGHT JOIN`. - `USING (col)` removes the duplicate column from `SELECT *`, which is usually desirable but surprises people relying on positional column access in ORM results. - A one-to-many join inflates aggregate functions unless you aggregate the many-side first in a subquery or CTE. `COUNT(*)` on an inflated join counts join rows, not entity rows. - Lateral joins are Postgres-specific syntax. The equivalent in MySQL is a correlated subquery in the `FROM` clause (allowed from MySQL 8.0.14). ## Related - [[backend/postgres]] - [[backend/postgres-indexes]] - [[backend/postgres-explain]] - [[cheatsheets/postgres-explain]] - [[cheatsheets/postgres-functions]] - [[cheatsheets/postgres-types]] - [[comparisons/postgres-vs-mysql]] - [[cheatsheets/index|Cheatsheets]]