What is a JOIN in SQL — and why LEFT JOIN quietly drops rows

There's a bug almost everyone hits. You write a LEFT JOIN to show every user — including those with no orders. It works. Then you add one condition to WHERE, and the users without orders vanish from the result. No error. They're just gone.
Let's unpack why — and what a JOIN does in the first place.
What a JOIN actually is
Data lives in separate tables: users here, orders there. That's correct — a user's name is stored once, and an order merely points at it.
But the page needs to say "Marina — 3 orders". That's data from two tables at once.
A JOIN is the instruction "stitch rows from these two tables wherever they correspond". You define the correspondence yourself: ON orders.user_id = users.id.
Without a JOIN you'd run two queries: fetch users, then fetch each user's orders. That's exactly how slow code works, and it even has a name — the N+1 problem, covered in the ORM explainer. A JOIN does the same thing in one trip.
INNER vs LEFT — the whole difference in one line
There are several JOIN types, but a beginner needs two.
INNER JOIN keeps only matches. The PostgreSQL docs put it this way: for each row of the first table, the result has a row for each row of the second that satisfies the condition. No order — the user never appears.
LEFT JOIN does that first, then picks up the stragglers. Verbatim from the docs: first an inner join is performed, then for each row in the first table that found no partner, a row is added with null values in the second table's columns. The conclusion: the result always has at least one row for every row of the left table.
In plain terms:
- INNER — "show users who have orders"
- LEFT — "show all users, with their orders alongside if any"
A user with no orders does appear under LEFT, but the order columns are empty. Not zero, not an empty string — NULL, "nothing here".
Trap 1: the LEFT JOIN that stopped being LEFT
Now, about those vanishing rows.
You want every user plus their August orders. You write:
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.created_at >= '2026-08-01';
And you lose everyone with no orders at all.
Why: the LEFT JOIN dutifully added them with NULL in orders.created_at. Then WHERE asked "is that date after August 1st?". NULL isn't "no" — it's "unknown". That comparison doesn't pass the filter. The row is thrown away.
Order matters: join first, filter second. The filter doesn't know you meant well.
The fix is to move the condition into the JOIN itself:
SELECT users.name, orders.total
FROM users
LEFT JOIN orders
ON orders.user_id = users.id
AND orders.created_at >= '2026-08-01';
The change looks cosmetic; the meaning is the opposite. The first version says "keep only rows with an August date". The second says "attach only August orders, but keep every user".
Simple rule: a condition about the left table goes in WHERE, a condition about the right table goes in ON.
Trap 2: JOINs multiply rows, and your total lies
The second mistake hurts more, because it looks like a correct answer.
A user has three orders. You join users to orders — and the user shows up three times. That's not a bug, it's precisely what the docs describe: one row per match.
While you're just printing a list, fine. Trouble starts when you count.
Join users to orders and to addresses. A user with 3 orders and 2 addresses produces 6 rows. The order total doubled. The report shows twice the real revenue, and nothing is highlighted as wrong.
How to spot it: if the total grew right after you added a second JOIN, while the data didn't change, you almost certainly hit this.
How to check by hand: run COUNT(*) before and after adding the join. The row count shouldn't grow if the relation is one-to-one.
How to fix it: aggregate one table at a time — a separate query or a subquery, not three joins at once.
What to actually do about it
Three habits that carry you a long way.
One: start with INNER, move to LEFT deliberately. If you can't say why you need rows with no partner, you want INNER.
Two: before computing a total, look at the raw rows without aggregates. Duplicates are visible to the naked eye in five seconds.
Three: if a query grows to three or more JOINs, split it. Readability beats one extra round trip to the database — especially while you're still learning to debug it.
What about CROSS JOIN?
That's a join with no condition at all: every row with every row. The docs do the math: with N and M rows you get N × M rows. Occasionally that's intentional, but more often it means you forgot the ON.
How is a JOIN different from a subquery?
A JOIN widens a row with data from a neighbouring table. A subquery usually returns a single value or a list to filter by. "Show the name and its orders" wants a JOIN; "show users who have at least one order" is often cleaner as a subquery.
Do I need JOINs if I use an ORM?
Yes. An ORM hides the syntax, not the meaning: include and select_related are the same joins. When a report shows a doubled total, you'll have to explain it in SQL terms, not library terms.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





