SQL Joins Practice

This distinction matters for OUTER joins:

• ON conditions are evaluated during the join
• WHERE conditions are evaluated after the join

For INNER JOINs, this doesn't matter—both produce the same result.

For OUTER JOINs, it's the difference between correct and silently wrong:

-- WRONG: Silently converts LEFT JOIN to INNER JOIN
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
-- Customers with no paid orders are REMOVED (WHERE drops NULL rows)

-- RIGHT: Keeps all customers; filters only matched orders
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid';
-- Customers with no paid orders show NULL for o.id

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

"Which customers have never ordered?" is a classic pattern:

SELECT c.*
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

How it works:

1. LEFT JOIN extends all customers with order data (or NULL if no match)
2. WHERE filters to only rows where the join found no match

This is often faster than NOT IN (which has NULL pitfalls) and equivalent to NOT EXISTS. See our filtering guide for the NOT EXISTS version.

SQL offers three ways to specify join conditions:

ON: Full flexibility

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Use ON when column names differ or you need complex conditions.

USING: Same column name shorthand

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

Behavior difference: USING de-duplicates the join column in SELECT *. With ON, you get both orders.customer_id and customers.customer_id; with USING, you get one customer_id.

NATURAL: Implicit join on all matching names

-- Joins on ALL columns with the same name
SELECT *
FROM orders
NATURAL JOIN customers;

Warning: NATURAL JOIN is dangerous. If you later add a column with the same name to both tables (like created_at), the join silently changes. Avoid in production code.

CROSS JOIN produces every combination of rows:

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

Equivalent forms:

-- Implicit (old-style)
SELECT * FROM sizes, colors;

-- Explicit with ON TRUE
SELECT * FROM sizes INNER JOIN colors ON TRUE;

Use cases:

• Generate all combinations for a product catalog
• Create a date spine for time-series analysis
• Fill in missing rows for reporting

Danger: Accidentally omitting a join condition creates a CROSS JOIN. If table A has 1,000 rows and table B has 1,000 rows, you get 1,000,000 rows. You can also use subqueries in the JOIN clause for more complex join conditions.

-- Employees and their managers (both in the same table)
SELECT
  e.id,
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id;

Common patterns:

• Employee → Manager hierarchy
• Comment → Parent comment (threaded discussions)
• Category → Parent category (nested categories)
• Row → Previous row (comparing sequential data)

The key is using table aliases (e, m) to distinguish the two "copies" of the table.

FULL OUTER JOIN keeps all rows from both tables:

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

Dialect support

MySQL workaround

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;

Note: Use UNION ALL with a WHERE clause (not plain UNION) for better performance—it avoids re-scanning for duplicates.

RIGHT JOIN is logically equivalent to LEFT JOIN with the tables swapped:

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

Best practice: Always use LEFT JOIN and adjust table order. This improves readability (the "main" table is always on the left) and is MySQL's recommendation for portability.

Last order per customer (with LEFT JOIN)

SELECT
  c.id,
  c.name,
  MAX(o.order_date) AS last_order_date,
  COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Note: Use COUNT(o.id) not COUNT(*). When there's no match, LEFT JOIN creates a row with NULLs. COUNT(*) counts that NULL row (returns 1), but COUNT(o.id) ignores it (returns 0).

Avoiding row explosion (pre-aggregate then join)

If you join orders to order_items, each order row multiplies by its item count. If you then SUM(order.amount), you overcount.

Solution: Aggregate in a subquery/CTE before joining. See our aggregation guide for detailed patterns.

1. Rows vanished? Check if a WHERE clause on the outer table is filtering NULL rows. Move the filter to ON.

2. Rows multiplied? You joined on a non-unique key. Check for duplicates in your join columns.

3. Wrong counts/sums? Join multiplication + aggregation. Pre-aggregate or use COUNT(DISTINCT).

4. All rows returned? Your ON condition might always be true (e.g., forgot to include the actual join key).

5. FULL OUTER JOIN failing? Check your database version—MySQL needs UNION workaround, SQLite needs 3.39+.

• PostgreSQL: JOIN Syntax
• MySQL: JOIN Clause
• SQLite: JOIN Syntax (v3.39 release notes)
• SQL Standard: USING vs NATURAL