LLM Best Practices

SQL Joins Cheatsheet

5 min read

View markdown source

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 postgres-indexes.

Join types

Pick the join that reflects which rows are required from each side.

Join typeRows returnedTypical use
INNER JOINOnly rows with a match on both sides.Require both entities to exist.
LEFT JOINAll rows from left; NULL for unmatched right.Optional right-side relationship.
RIGHT JOINAll rows from right; NULL for unmatched left.Rarely preferred; rewrite as LEFT JOIN with tables swapped.
FULL OUTER JOINAll rows from both sides; NULL for missing side.Reconcile two data sources with partial overlap.
CROSS JOINCartesian product; every left row paired with every right row.Combination generation; no ON clause.
SELF JOINTable joined to itself via aliases.Hierarchy traversal, adjacency lists, finding duplicates.
LATERAL JOINRight side is a subquery that references each left row.Per-row subqueries, LIMIT per group.
-- 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.

DiagramPatternWhat to write
Intersection onlyBoth sides matchINNER JOIN ... ON ...
Left circle onlyLeft side, no match on rightLEFT JOIN ... WHERE right.id IS NULL
Right circle onlyRight side, no match on leftRIGHT JOIN ... WHERE left.id IS NULL
Left circle fullAll left, matched right is optionalLEFT JOIN
Right circle fullAll right, matched left is optionalRIGHT JOIN (or swap to LEFT JOIN)
Full unionAll rows from both sidesFULL OUTER JOIN
Outer ring onlyRows not in the intersectionFULL 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.

-- 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.

PracticeReason
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.

BugSymptomFix
NULL comparison with =WHERE a.id = NULL never matches.Use IS NULL / IS NOT NULL.
Filtering a LEFT JOIN in WHEREWHERE right.col = 'x' turns the LEFT JOIN into an INNER JOIN.Move the filter into the ON clause.
One-to-many join without aggregationResult has more rows than expected; aggregates are inflated.Add GROUP BY or use a subquery to aggregate first.
CROSS JOIN without ONRow 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 keySeq scan on large tables; slow queries.Add a B-tree index on the foreign key column.
-- 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).

Graph View

Backlinks