SQL joins explained visually, plus the mistakes that break reports

Joins combine rows from two tables based on a related column. Almost every analysis uses them, and almost every "why don't these numbers match?" conversation ends with a join that behaved differently than expected. This guide covers each type with a picture, an example and a note on when to use it.

The examples use two tables: customers (one row per customer) and orders (one row per order, with a customer_id).

INNER JOIN: only matching rows

Returns rows where the key exists in both tables. Customers without orders, and orders without a known customer, disappear.

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id;

LEFT JOIN: everything from the left, matches from the right

The workhorse of analytics. Keeps every customer, and fills order columns with NULL where there is no match.

SELECT c.customer_name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_name;

The WHERE clause trap

Filtering the right-hand table in WHERE silently turns a left join back into an inner join, because rows with NULL values fail the condition.

-- Loses customers with no 2026 orders
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

-- Keeps every customer: put the filter in the ON clause
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
   AND o.order_date >= '2026-01-01';

RIGHT and FULL OUTER JOIN

A RIGHT JOIN is a left join with the tables swapped; most teams rewrite them as left joins for readability. A FULL OUTER JOIN keeps everything from both sides, which is ideal for reconciling two systems.

-- Which orders exist in the shop but not in finance, and vice versa?
SELECT
    COALESCE(s.order_id, f.order_id) AS order_id,
    s.amount AS shop_amount,
    f.amount AS finance_amount
FROM shop_orders s
FULL OUTER JOIN finance_orders f
    ON f.order_id = s.order_id
WHERE s.order_id IS NULL OR f.order_id IS NULL OR s.amount <> f.amount;

Anti joins and semi joins

These answer "which rows have no match?" and "which rows have at least one match?". NOT EXISTS and EXISTS are the clearest and safest way to write them.

-- Anti join: customers who have never ordered
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Semi join: customers with at least one order, without duplicating them
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

CROSS JOIN: every combination

Pairs every row with every other row. Useful for building complete grids, such as every product for every day, so missing days show as zero instead of disappearing.

SELECT d.date, p.product_id, COALESCE(SUM(s.qty), 0) AS qty
FROM dim_date d
CROSS JOIN dim_product p
LEFT JOIN sales s ON s.date = d.date AND s.product_id = p.product_id
GROUP BY d.date, p.product_id;

Join fan-out: the silent report killer

If the right-hand table has several rows per key, each left row is repeated. Join orders to order_items and then sum the order total, and every multi-item order is counted several times.

-- Wrong: order amount repeated once per item
SELECT SUM(o.amount) FROM orders o JOIN order_items i ON i.order_id = o.order_id;

-- Right: aggregate to the same grain before joining
WITH items AS (
    SELECT order_id, COUNT(*) AS item_count
    FROM order_items
    GROUP BY order_id
)
SELECT SUM(o.amount) AS revenue, SUM(i.item_count) AS items
FROM orders o
LEFT JOIN items i ON i.order_id = o.order_id;

A quick safety check: compare COUNT(*) before and after a join. If the row count grows when you did not expect it to, you have a fan-out. Defining the grain of every table, as covered in the star schema guide, prevents most of these problems.

Practise these patterns with the SQL puzzles, and try them on your own files in SQL Studio.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 30 September 2026. About me