SQL joins explained with row counts and common mistakes

Almost every SQL tutorial teaches joins with overlapping circles. It looks intuitive and it teaches the wrong mental model, because the thing that goes wrong in real work — your row count changing unexpectedly — cannot be drawn as set overlap.

This tutorial teaches joins by tracking row counts, which is how experienced analysts actually catch a wrong result.

Set up

Two small tables. Deliberately small, so you can count by hand.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50),
  city        VARCHAR(50)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  amount      DECIMAL(10,2)
);

INSERT INTO customers VALUES
  (1, 'Anita',  'Delhi'),
  (2, 'Rahul',  'Mumbai'),
  (3, 'Priya',  'Delhi'),
  (4, 'Vikram', 'Pune');

INSERT INTO orders VALUES
  (101, 1, 5000.00),
  (102, 1, 3000.00),
  (103, 2, 7500.00),
  (104, 5, 2000.00);

Note two things deliberately built into this data. Anita has two orders. Order 104 belongs to customer 5, who does not exist in customers. Both will matter.

Customers: 4 rows. Orders: 4 rows.

INNER JOIN — only matches survive

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

Result: 3 rows. Anita twice, Rahul once.

Count what disappeared. Priya and Vikram vanish because they have no orders. Order 104 vanishes because customer 5 does not exist.

That second disappearance is the dangerous one. If you were summing revenue, you just silently lost ₹2,000 and nothing warned you.

LEFT JOIN — keep everything on the left

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result: 5 rows. Anita twice, Rahul once, Priya with NULLs, Vikram with NULLs.

Every customer is preserved. Order 104 is still missing, because it has no matching customer and we kept the customer side.

The mistake that turns a LEFT JOIN into an INNER JOIN

This one appears in production code constantly.

-- Looks like a LEFT JOIN. Behaves like an INNER JOIN.
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 1000;

Result: 3 rows. Priya and Vikram are gone.

Why? The LEFT JOIN gave them NULL for amount. Then WHERE o.amount > 1000 evaluated NULL > 1000, which is not true, so those rows were filtered out. The preservation you asked for was undone by the very next line.

The fix is to move the condition into the ON clause, so it filters what to match rather than what to keep:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.amount > 1000;

Result: 5 rows. Priya and Vikram return with NULLs.

The rule: conditions on the right-hand table belong in ON. Conditions on the left-hand table belong in WHERE. Getting this backwards is one of the two most common join bugs.

Fan-out — the other one

Here is the bug that actually costs money.

SELECT COUNT(*) FROM customers;              -- 4
SELECT COUNT(*)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;  -- 5

The row count went up. Anita matched two orders, so her row was duplicated.

That is fine here, because we wanted order-level rows. It stops being fine the moment you aggregate something from the left table:

-- WRONG. Anita's credit limit is counted twice.
SELECT SUM(c.credit_limit)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Any customer with multiple orders has their credit limit added once per order. The total is inflated, no error is raised, and the number looks plausible.

The habit that prevents it: check the row count before and after every join. If it changed and you did not expect it to, stop and find out why before writing anything else.

If you need a fan-out-safe aggregate, collapse the right side first:

SELECT SUM(c.credit_limit)
FROM customers c
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
) o ON c.customer_id = o.customer_id;

Now o has at most one row per customer, and the join cannot duplicate anything.

RIGHT and FULL joins

RIGHT JOIN is a LEFT JOIN with the tables swapped. Almost nobody uses it, because reading a query where preservation runs right-to-left is harder for no benefit. Write LEFT and reorder your tables.

FULL OUTER JOIN keeps unmatched rows from both sides — in our data, that is Priya, Vikram and order 104. It is genuinely useful for one specific job: reconciling two sources to find what exists in one and not the other.

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

That query lists exactly the mismatches. MySQL does not support FULL OUTER JOIN; emulate it with a UNION of a LEFT and a RIGHT join.

Finding orphaned rows

Order 104 has been invisible in almost every query above. Here is how to find records like it:

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

This anti-join pattern — LEFT JOIN then filter for NULL on the right — is how you audit referential integrity. Run it before trusting any revenue total.

What to take away

Four habits, in order of how much trouble they save.

1. Count rows before and after every join. An unexpected change is a bug until proven otherwise.
2. Put right-table conditions in ON, left-table conditions in WHERE.
3. Aggregate the many-side down before joining when you need to sum something from the one-side.
4. Anti-join to find orphans before trusting a total.

Master those and joins stop being a source of quiet wrong answers — which is what they are for most people who learned them from a diagram.