SQLCheat Sheet

SQL Joins Cheat Sheet

Quick-reference for every JOIN type you need in practice. Each section includes copy-ready snippets with inline output comments.

INNER JOIN

Returns only rows that have a match in both tables. Unmatched rows are excluded entirely.

Basic INNER JOIN

SELECT c.name, o.order_date, o.total
FROM customers c
INNER JOIN orders o
  ON o.customer_id = c.id;

JOIN is shorthand for INNER JOIN

-- These are identical:
SELECT * FROM a JOIN b ON a.id = b.a_id;
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

LEFT JOIN

Keeps all rows from the left table. Unmatched right-table columns are filled with NULL.

Basic LEFT JOIN

-- Customers with no orders appear with NULL order columns
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id;

Filter on right table: ON vs WHERE

-- CORRECT: Keeps all customers, joins only paid orders
SELECT c.name, o.id AS paid_order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid';

-- WRONG: Silently becomes INNER JOIN!
-- WHERE drops NULL rows from unmatched customers
SELECT c.name, o.id AS paid_order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';  -- removes customers!

For LEFT/RIGHT joins, put filters on the optional table in ON, not WHERE.

RIGHT JOIN

Keeps all rows from the right table. Logically equivalent to LEFT JOIN with swapped table order.

RIGHT JOIN (rarely used)

-- These are equivalent:
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT * FROM b LEFT JOIN a ON a.id = b.a_id;

Most teams use LEFT JOIN exclusively. Swap table order instead of using RIGHT JOIN.

FULL OUTER JOIN

Keeps all rows from both tables. Unmatched sides are filled with NULL.

FULL OUTER JOIN (PostgreSQL, SQL Server)

-- Reconciliation: find mismatches between systems
SELECT
  a.id AS system_a_id,
  b.id AS system_b_id,
  COALESCE(a.amount, 0) AS amount_a,
  COALESCE(b.amount, 0) AS amount_b
FROM system_a a
FULL OUTER JOIN system_b b
  ON a.external_id = b.external_id;

MySQL workaround (no native FULL OUTER JOIN)

SELECT * FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
UNION ALL
SELECT * FROM table_a a
RIGHT JOIN table_b b ON a.id = b.a_id
WHERE a.id IS NULL;

MySQL does not support FULL OUTER JOIN. Use UNION ALL of LEFT + RIGHT with a WHERE filter.

CROSS JOIN

Produces the Cartesian product — every row from A paired with every row from B.

Generate all combinations

-- 3 sizes x 4 colors = 12 rows
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Equivalent forms

-- All three produce the same Cartesian product:
SELECT * FROM sizes CROSS JOIN colors;
SELECT * FROM sizes, colors;
SELECT * FROM sizes INNER JOIN colors ON TRUE;

Accidentally omitting an ON clause creates a CROSS JOIN. 1000 x 1000 = 1 million rows.

Self-Join

Join a table to itself using aliases. Common for hierarchies and row comparisons.

Employee to manager hierarchy

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id;

Threaded comments (parent-child)

SELECT
  c.id,
  c.body,
  p.body AS parent_body
FROM comments c
LEFT JOIN comments p
  ON c.parent_id = p.id;

Join Conditions: ON vs USING

ON accepts any condition. USING requires identical column names and de-duplicates them in SELECT *.

ON (full flexibility)

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Result includes both o.customer_id and c.id

USING (same column name shorthand)

-- Requires both tables have customer_id
SELECT *
FROM orders
JOIN customers USING (customer_id);
-- Result includes a single customer_id column

Multiple join keys

-- ON with compound condition
SELECT *
FROM line_items li
JOIN prices p
  ON p.product_id = li.product_id
 AND p.region = li.region;

Anti-Join Pattern

Find rows in one table with no corresponding rows in another.

LEFT JOIN + WHERE IS NULL

-- Customers who have never ordered
SELECT c.*
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

NOT EXISTS (equivalent, NULL-safe)

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Prefer NOT EXISTS over NOT IN — NOT IN silently returns no rows if the subquery contains NULL.

Multi-Table Joins

Chain joins to combine three or more tables. Read left to right: each JOIN adds one table.

Three-table join

SELECT
  o.id AS order_id,
  c.name AS customer,
  p.name AS product
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON i.product_id = p.id;

Mixed join types

-- LEFT JOIN for optional data, INNER for required
SELECT
  c.name,
  o.id AS order_id,
  s.tracking_number
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN shipments s ON s.order_id = o.id;

An INNER JOIN after a LEFT JOIN can discard preserved NULL rows — check your join chain.

Join with Aggregation

Combine joins with GROUP BY. Watch out for row multiplication inflating counts and sums.

COUNT per group with LEFT JOIN

-- COUNT(o.id) returns 0 for customers with no orders
-- COUNT(*) would return 1 (counts the NULL row)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Avoid row multiplication (pre-aggregate)

-- WRONG: if orders have multiple items, SUM is inflated
-- RIGHT: aggregate in CTE first, then join
WITH order_totals AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, COALESCE(ot.revenue, 0) AS revenue
FROM customers c
LEFT JOIN order_totals ot ON ot.customer_id = c.id;

Joining before aggregating can multiply rows. Aggregate in a subquery/CTE first.

Learn SQL in Depth

SQL Filtering Practice →SQL Aggregation Practice →SQL Subqueries Practice →

Warm-up1 / 2

Can you write this from memory?

Write a query to join the orders table with the users table on user_id, selecting all columns.

Start Practicing SQL

Free daily exercises with spaced repetition. No credit card required.

← Back to SQL Syntax Practice