Joins — Cheat Sheet

SQL · 8 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Joins

SQL8 topicsQuick revision reference

INNER JOIN

INNER JOIN returns only rows where the join condition matches in both tables, excluding unmatched rows from either side.

✓INNER JOIN returns only rows with matching values in both tables.
✓Always use explicit JOIN ... ON syntax; avoid implicit comma joins.
✓Index the FK column on the child table to enable fast index nested-loop joins.
✓Extra filter conditions can go in the ON clause or the WHERE clause for INNER JOIN — semantically identical.
✓Join algorithms: nested loop (small), hash join (large unsorted), merge join (sorted inputs).
✓In JPA, use JOIN FETCH to load associations in a single query and avoid N+1 selects.

SQL — explicit JOIN vs implicit comma join

-- Implicit (old-style, avoid): comma join with WHERE
SELECT o.id, u.email, o.total_amount
FROM orders o, users u
WHERE o.user_id = u.id    -- this IS the join condition
  AND o.status = 'pending';

-- Explicit (preferred): JOIN ... ON
SELECT o.id, u.email, o.total_amount
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';

-- Multi-column join condition
SELECT oi.order_id, p.name, oi.quantity
FROM order_items oi
INNER JOIN products p ON p.id = oi.product_id
                      AND p.is_active = TRUE;   -- extra filter in ON clause

-- Self-describing aliases matter
SELECT
    o.id          AS order_id,
    o.total_amount,
    u.email       AS customer_email,
    u.username    AS customer_name
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

LEFT & RIGHT OUTER JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right-side columns are NULL — RIGHT JOIN is the mirror image.

✓LEFT JOIN: all rows from left table; NULL for unmatched right-table columns.
✓Filter on right-table column in WHERE converts LEFT JOIN to INNER JOIN — put it in ON instead.
✓Use LEFT JOIN + WHERE right_col IS NULL to find rows with no matching child (anti-join).
✓Any RIGHT JOIN can be written as a LEFT JOIN by swapping tables — prefer LEFT JOIN.
✓Chained LEFT JOINs preserve "parent rows without children" through the full chain.
✓In JPA, LEFT JOIN FETCH loads optional associations without discarding parent entities.

SQL — LEFT JOIN, the NULL filter trap, and anti-join

-- LEFT JOIN: all users and their order count (0 if no orders)
SELECT
    u.id,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

-- Trap: WHERE on right-table column kills the LEFT JOIN!
-- This returns only users WHO HAVE orders (= INNER JOIN behavior)
SELECT u.id, u.email, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'pending';   -- NULL rows filtered out → accidental INNER JOIN

-- Fix: move the filter into the ON clause
SELECT u.id, u.email, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
                  AND o.status = 'pending';   -- users with no pending orders still appear (o cols = NULL)

-- Find users who have NEVER placed an order (anti-join using LEFT JOIN)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;   -- o.id is NULL only for unmatched users

FULL OUTER JOIN & UNION Emulation

FULL OUTER JOIN returns all rows from both tables with NULLs for unmatched sides; it can be emulated in MySQL using UNION of LEFT and RIGHT JOINs.

✓FULL OUTER JOIN = all rows from both tables; NULLs fill unmatched columns on either side.
✓MySQL does not support FULL OUTER JOIN — emulate with LEFT JOIN UNION RIGHT JOIN WHERE IS NULL.
✓UNION deduplicates rows (costly); UNION ALL retains duplicates (faster — prefer when safe).
✓Use COALESCE on the key column to get a non-NULL identifier from either side.
✓IS DISTINCT FROM (PostgreSQL) is a NULL-safe != operator useful in reconciliation queries.
✓FULL OUTER JOIN is the standard pattern for data reconciliation and audit comparisons.

SQL — FULL OUTER JOIN for data reconciliation (PostgreSQL)

-- PostgreSQL: reconcile two product tables (compare legacy vs new system)
SELECT
    COALESCE(legacy.sku, new_sys.sku)   AS sku,
    legacy.price                          AS legacy_price,
    new_sys.price                         AS new_price,
    CASE
        WHEN legacy.sku IS NULL THEN 'Only in new system'
        WHEN new_sys.sku IS NULL THEN 'Only in legacy'
        ELSE 'In both'
    END AS status
FROM legacy_products legacy
FULL OUTER JOIN new_products new_sys ON new_sys.sku = legacy.sku
WHERE legacy.price IS DISTINCT FROM new_sys.price   -- PostgreSQL: NULL-safe !=
   OR legacy.sku IS NULL
   OR new_sys.sku IS NULL;

SELF JOIN

A self-join joins a table to itself using different aliases, used for hierarchical data (employee-manager), sequential comparisons, and finding related rows within the same table.

✓A self-join joins a table to itself using two different aliases.
✓Use LEFT self-join for hierarchy queries so root nodes (no manager) are included.
✓Use id comparison (p2.id > p1.id) to avoid generating duplicate pairs (A,B) and (B,A).
✓Fixed-depth hierarchies use self-joins; variable-depth hierarchies need recursive CTEs.
✓Self-joins can be expensive on large tables — ensure the join column is indexed.
✓Window functions (LAG/LEAD) are often cleaner than self-joins for comparing adjacent rows.

SQL — self-join for employee-manager hierarchy

-- Employee with their manager's name
SELECT
    e.id          AS employee_id,
    e.full_name   AS employee_name,
    m.full_name   AS manager_name,
    d.name        AS department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id   -- LEFT JOIN: top-level employees have NULL manager
JOIN departments d ON d.id = e.department_id;

-- Find employees who earn more than their manager
SELECT
    e.full_name    AS employee,
    e.salary       AS employee_salary,
    m.full_name    AS manager,
    m.salary       AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

-- Two-level deep hierarchy (rigid depth — use recursive CTE for variable depth)
SELECT
    grandchild.full_name   AS employee,
    parent.full_name       AS manager,
    grandparent.full_name  AS director
FROM employees grandchild
JOIN employees parent      ON parent.id      = grandchild.manager_id
JOIN employees grandparent ON grandparent.id = parent.manager_id;

CROSS JOIN

CROSS JOIN produces the Cartesian product of two tables — every row from the left table paired with every row from the right, yielding m×n rows.

✓CROSS JOIN = Cartesian product: m × n rows with no join predicate.
✓Intentional use: date spine generation, all-pairs combination tables, test data.
✓Accidental Cartesian product: implicit join with a missing WHERE condition.
✓PostgreSQL generate_series() is a cleaner alternative for date/integer sequences.
✓Always estimate the output row count (m × n) before running a CROSS JOIN on large tables.
✓Accidental Cartesian products return "correct-looking" wrong results — they do not error.

SQL — CROSS JOIN and date-spine generation

-- Explicit CROSS JOIN: all size-colour combinations for a product catalogue
SELECT
    s.size_name,
    c.colour_name
FROM sizes s
CROSS JOIN colours c
ORDER BY s.size_name, c.colour_name;
-- If sizes has 5 rows and colours has 8 rows → 40 rows

-- Date spine: generate a row for every (user, date) pair in the last 7 days
-- Then LEFT JOIN to actual orders to show "0 orders" days
WITH date_spine AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '6 days',
        CURRENT_DATE,
        '1 day'::INTERVAL
    )::DATE AS day
),
active_users AS (
    SELECT id, email FROM users WHERE status = 'active'
)
SELECT
    au.email,
    ds.day,
    COALESCE(SUM(o.total_amount), 0) AS daily_revenue
FROM active_users au
CROSS JOIN date_spine ds
LEFT JOIN orders o ON o.user_id = au.id
                  AND o.created_at::DATE = ds.day
GROUP BY au.email, ds.day
ORDER BY au.email, ds.day;

Joining 3+ Tables

Joining three or more tables requires deliberate ordering, clear aliasing, and understanding how join results flow left-to-right through the FROM clause.

✓Joins are logically left-associative; each result feeds the next JOIN as a virtual table.
✓Always alias every table in multi-table queries to prevent ambiguous column errors.
✓Joining through a junction table (many-to-many) without GROUP BY duplicates parent rows.
✓Use LEFT JOIN for optional relationships to preserve parent rows without children.
✓The planner may reorder joins for performance — EXPLAIN shows the actual order chosen.
✓Keep the most selective join (smallest result set) early to reduce intermediate row counts.

SQL — joining 4 tables with clear aliasing

-- Order report: order details + customer info + product info
SELECT
    o.id                    AS order_id,
    o.created_at,
    o.total_amount,
    u.email                 AS customer_email,
    u.username,
    p.name                  AS product_name,
    p.category,
    oi.quantity,
    oi.unit_price
FROM orders o
INNER JOIN users        u  ON u.id         = o.user_id
INNER JOIN order_items  oi ON oi.order_id  = o.id
INNER JOIN products     p  ON p.id         = oi.product_id
WHERE o.status = 'completed'
  AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC;

-- Adding department info to employees (4 tables)
SELECT
    e.full_name,
    e.salary,
    d.name        AS department,
    loc.city      AS office_city,
    m.full_name   AS manager_name
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN locations   loc ON loc.id = d.location_id
LEFT JOIN employees m ON m.id = e.manager_id   -- LEFT: top-level employees have no manager
ORDER BY d.name, e.full_name;

JOIN Performance

JOIN performance depends on join algorithm (nested loop, hash, merge), index availability on join columns, and row estimates from table statistics — understanding EXPLAIN output is essential.

✓Nested Loop: good for small outer tables with indexed inner lookups — O(n log n).
✓Hash Join: good for large unsorted tables — O(n) but requires work_mem.
✓Merge Join: good for pre-sorted inputs (index scans) — O(n log n).
✓Missing FK index forces Hash Join or Seq Scan on the inner table — always index FK columns.
✓Stale statistics cause wrong row estimates and wrong algorithm choices — run ANALYZE regularly.
✓Hash join spilling to disk ("Batches > 1") is a signal to increase work_mem.

SQL — EXPLAIN ANALYZE join algorithm output, annotated

-- EXPLAIN ANALYZE: the authoritative source for join algorithm and cost
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email, o.total_amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed';

/* Sample EXPLAIN output (annotated):
Hash Join  (cost=1200.00..3400.00 rows=50000 width=48)  ← planner estimated cost
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0.00..1800.00 rows=50000)
        Filter: (status = 'completed')
        Rows Removed by Filter: 150000
  ->  Hash  (cost=800.00..800.00 rows=20000)
        ->  Seq Scan on users u (cost=0.00..800.00 rows=20000)

Analysis:
- Hash Join chosen: no usable index on o.user_id (missing FK index!)
- After adding: CREATE INDEX idx_orders_user_id ON orders(user_id);
  → planner switches to Index Nested Loop
*/

-- Force planner to use specific strategy (PostgreSQL — for testing only)
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
-- Now only nested loop is available; useful to compare actual runtimes

Anti-JOIN Pattern

An anti-join returns rows from one table that have no match in another; the three implementations (NOT EXISTS, NOT IN, LEFT JOIN IS NULL) have different semantics and performance profiles.

✓Anti-join returns rows from A with no match in B — "A minus B" semantics.
✓NOT EXISTS is the safest and typically fastest — use as the default.
✓NOT IN returns zero rows if the subquery contains any NULL — a common silent bug.
✓LEFT JOIN IS NULL is equivalent to NOT EXISTS when the join column is non-nullable.
✓PostgreSQL implements NOT EXISTS as Hash Anti Join — short-circuits on first match.
✓Always add WHERE subquery_col IS NOT NULL when using NOT IN to be safe.

SQL — NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL

-- Find users who have never placed an order

-- 1. NOT EXISTS (recommended — safe with NULLs, often fastest)
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 2. NOT IN (dangerous if orders.user_id can be NULL)
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);
-- If any row in orders has user_id = NULL → returns 0 rows!
-- Safe only when the subquery column is declared NOT NULL

-- 3. LEFT JOIN IS NULL (readable, portable)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Products that have never been ordered
SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/sql

Cheat Sheet

Joins

SQL

8 topics · swipe →

AiCanCode.org

Joins

INNER JOIN

✓INNER JOIN returns only rows with matching values in both tables.
✓Always use explicit JOIN ... ON syntax; avoid implicit comma joins.
✓Index the FK column on the child table to enable fast index nested-loop joins.
✓Extra filter conditions can go in the ON clause or the WHERE clause for INNER JOIN — semantically identical.
✓Join algorithms: nested loop (small), hash join (large unsorted), merge join (sorted inputs).

-- Implicit (old-style, avoid): comma join with WHERE
SELECT o.id, u.email, o.total_amount
FROM orders o, users u
WHERE o.user_id = u.id    -- this IS the join condition
  AND o.status = 'pending';

-- Explicit (preferred): JOIN ... ON
SELECT o.id, u.email, o.total_amount
FROM orders o
…

AiCanCode.org

Joins

LEFT & RIGHT OUTER JOIN

✓LEFT JOIN: all rows from left table; NULL for unmatched right-table columns.
✓Filter on right-table column in WHERE converts LEFT JOIN to INNER JOIN — put it in ON instead.
✓Use LEFT JOIN + WHERE right_col IS NULL to find rows with no matching child (anti-join).
✓Any RIGHT JOIN can be written as a LEFT JOIN by swapping tables — prefer LEFT JOIN.
✓Chained LEFT JOINs preserve "parent rows without children" through the full chain.

-- LEFT JOIN: all users and their order count (0 if no orders)
SELECT
    u.id,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

…

AiCanCode.org

Joins

FULL OUTER JOIN & UNION Emulation

✓FULL OUTER JOIN = all rows from both tables; NULLs fill unmatched columns on either side.
✓MySQL does not support FULL OUTER JOIN — emulate with LEFT JOIN UNION RIGHT JOIN WHERE IS NULL.
✓UNION deduplicates rows (costly); UNION ALL retains duplicates (faster — prefer when safe).
✓Use COALESCE on the key column to get a non-NULL identifier from either side.
✓IS DISTINCT FROM (PostgreSQL) is a NULL-safe != operator useful in reconciliation queries.

-- PostgreSQL: reconcile two product tables (compare legacy vs new system)
SELECT
    COALESCE(legacy.sku, new_sys.sku)   AS sku,
    legacy.price                          AS legacy_price,
    new_sys.price                         AS new_price,
    CASE
        WHEN legacy.sku IS NULL THEN 'Only in new system'
        WHEN new_sys.sku IS NULL THEN 'Only in legacy'
        ELSE 'In both'
…

AiCanCode.org

Joins

SELF JOIN

✓A self-join joins a table to itself using two different aliases.
✓Use LEFT self-join for hierarchy queries so root nodes (no manager) are included.
✓Use id comparison (p2.id > p1.id) to avoid generating duplicate pairs (A,B) and (B,A).
✓Fixed-depth hierarchies use self-joins; variable-depth hierarchies need recursive CTEs.
✓Self-joins can be expensive on large tables — ensure the join column is indexed.

-- Employee with their manager's name
SELECT
    e.id          AS employee_id,
    e.full_name   AS employee_name,
    m.full_name   AS manager_name,
    d.name        AS department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id   -- LEFT JOIN: top-level employees have NULL manager
JOIN departments d ON d.id = e.department_id;
…

AiCanCode.org

Joins

CROSS JOIN

✓CROSS JOIN = Cartesian product: m × n rows with no join predicate.
✓Intentional use: date spine generation, all-pairs combination tables, test data.
✓Accidental Cartesian product: implicit join with a missing WHERE condition.
✓PostgreSQL generate_series() is a cleaner alternative for date/integer sequences.
✓Always estimate the output row count (m × n) before running a CROSS JOIN on large tables.

-- Explicit CROSS JOIN: all size-colour combinations for a product catalogue
SELECT
    s.size_name,
    c.colour_name
FROM sizes s
CROSS JOIN colours c
ORDER BY s.size_name, c.colour_name;
-- If sizes has 5 rows and colours has 8 rows → 40 rows

…

AiCanCode.org

Joins

Joining 3+ Tables

✓Joins are logically left-associative; each result feeds the next JOIN as a virtual table.
✓Always alias every table in multi-table queries to prevent ambiguous column errors.
✓Joining through a junction table (many-to-many) without GROUP BY duplicates parent rows.
✓Use LEFT JOIN for optional relationships to preserve parent rows without children.
✓The planner may reorder joins for performance — EXPLAIN shows the actual order chosen.

-- Order report: order details + customer info + product info
SELECT
    o.id                    AS order_id,
    o.created_at,
    o.total_amount,
    u.email                 AS customer_email,
    u.username,
    p.name                  AS product_name,
    p.category,
…

AiCanCode.org

Joins

JOIN Performance

✓Nested Loop: good for small outer tables with indexed inner lookups — O(n log n).
✓Hash Join: good for large unsorted tables — O(n) but requires work_mem.
✓Merge Join: good for pre-sorted inputs (index scans) — O(n log n).
✓Missing FK index forces Hash Join or Seq Scan on the inner table — always index FK columns.
✓Stale statistics cause wrong row estimates and wrong algorithm choices — run ANALYZE regularly.

-- EXPLAIN ANALYZE: the authoritative source for join algorithm and cost
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email, o.total_amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed';

/* Sample EXPLAIN output (annotated):
Hash Join  (cost=1200.00..3400.00 rows=50000 width=48)  ← planner estimated cost
…

AiCanCode.org

Joins

Anti-JOIN Pattern

✓Anti-join returns rows from A with no match in B — "A minus B" semantics.
✓NOT EXISTS is the safest and typically fastest — use as the default.
✓NOT IN returns zero rows if the subquery contains any NULL — a common silent bug.
✓LEFT JOIN IS NULL is equivalent to NOT EXISTS when the join column is non-nullable.
✓PostgreSQL implements NOT EXISTS as Hash Anti Join — short-circuits on first match.

-- Find users who have never placed an order

-- 1. NOT EXISTS (recommended — safe with NULLs, often fastest)
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

…

AiCanCode.org

Master Joins

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/sql