The JOIN That Quietly Dropped Half Your Data

You wrote a query to list your customers and their orders. It ran fine — but half your customers vanished from the results. The ones who never placed an order simply weren't there. Nothing errored, nothing warned you. The data was just quietly wrong.

That is almost always a JOIN problem. JOINs are how you pull rows from two tables together, and the type of JOIN you pick decides who makes it into the result and who gets silently dropped. Get it right and your reports are trustworthy. Get it wrong and you ship a dashboard that under-counts every month without anyone noticing.

This guide walks through the four JOINs you'll actually use, with a tiny dataset you can picture in your head, so the difference stops being abstract.

The two tables we'll use

Imagine an online shop. One table holds customers, the other holds orders. Every order points back to a customer through customer_id.

-- customers
id | name
1  | Alice
2  | Bob
3  | Carol   -- signed up, never ordered

-- orders
id | customer_id | amount
10 | 1           | 40
11 | 1           | 25
12 | 2           | 90
13 | 99          | 15   -- customer_id 99 doesn't exist (orphan)

Notice the two edge cases baked in on purpose: Carol is a customer with no orders, and order 13 belongs to a customer who isn't in the table. Those two rows are exactly where JOIN types disagree, so keep an eye on them.

INNER JOIN: only the matches

An INNER JOIN returns rows only when the join condition is satisfied on both sides. If a customer has no order, or an order has no matching customer, that row is excluded.

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

Result:

Carol is gone (no order), and the orphan order 13 is gone (no customer). This is the JOIN people reach for by default, and most of the time it's correct. The trap is using it when you actually needed to keep the non-matching rows — like when you're counting customers and assume everyone placed an order.

INNER JOIN answers "where do these two things overlap?" — not "show me everyone."

LEFT JOIN: keep everything on the left

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the first table, whether or not it finds a match on the right. When there's no match, the right-side columns come back as NULL.

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

Result:

Now Carol appears with a NULL amount, because she's a real customer even though she never bought anything. This is the JOIN you want for "list all customers and their total spending, including the ones who spent nothing." Order 13 is still absent — it's on the right side, and LEFT JOIN doesn't preserve unmatched right-side rows.

LEFT JOIN is also the standard way to find the gaps. Want customers who have never ordered? Keep everyone, then filter to the ones whose order side came back empty:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- no matching order

That returns just Carol. This "LEFT JOIN + IS NULL" pattern is worth memorizing — it answers a huge share of real questions ("users with no purchase," "products never sold," "employees with no manager").

RIGHT JOIN: keep everything on the right

A RIGHT JOIN is the mirror image: it keeps every row from the second table and fills the left side with NULL where there's no match.

SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Result:

Here the orphan order 13 finally shows up, with a NULL name because customer 99 doesn't exist. That's useful for a data-quality check: "which orders point at a customer we don't have?"

In practice, most people rarely write RIGHT JOIN. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and reading queries is easier when the table you care about is always on the left. Treat RIGHT JOIN as something you should recognize, not necessarily something you need to reach for.

FULL OUTER JOIN: keep both sides

A FULL OUTER JOIN keeps every row from both tables, matching them where it can and filling NULL on whichever side is missing.

SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Result:

Both edge cases survive: Carol (customer, no order) and order 13 (order, no customer). This is exactly what you want when reconciling two systems and you need to see mismatches from either direction at once.

One caveat: MySQL doesn't support FULL OUTER JOIN directly. PostgreSQL, SQL Server, and Oracle do. In MySQL you emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION:

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

A mental model that sticks

Forget the Venn-diagram pictures for a second and think about direction instead:

• INNER — only rows that match on both sides.
• LEFT — all of the first table, plus matches from the second.
• RIGHT — all of the second table, plus matches from the first.
• FULL — everything from both, matched where possible.

The single most common bug is reaching for INNER JOIN when you meant LEFT JOIN and quietly losing the rows that had no match — the Carols of your dataset. Whenever a count comes back lower than you expected, that's the first thing to check.

Two more habits keep JOINs honest. First, always qualify your columns with table aliases (c.id, o.amount) so you never confuse two columns that share a name. Second, when you filter an outer join, remember that putting a right-table condition in WHERE can silently turn your LEFT JOIN back into an INNER JOIN — because WHERE o.amount > 20 throws away the NULL rows you were trying to keep. If you want to filter the right side before matching, put the condition in the ON clause instead.

Wrapping up

JOINs aren't really about syntax — they're about deciding, on purpose, who stays and who gets dropped. INNER keeps the overlap, LEFT keeps your main table intact, RIGHT flips the direction, and FULL keeps everyone. Once you can look at a query and predict where Carol and the orphan order will land, you're reading JOINs the way the database does.

Next time a report looks a little too clean, count your rows before and after the JOIN. The gap between those two numbers is usually the story.