How SQL JOIN works: matching rows, INNER vs LEFT, NULLs

The idea

A JOIN answers one question for every row of the first table: which rows of the second table go with this one? You tell it how to decide in the ON clause. The database then makes one result row for every pair that passes the test.

That is all a JOIN is. There is no merging of tables side by side, and no row lines up with "the row next to it". Each row of customers is compared with the rows of orders, and every match turns into a result row that carries the columns of both.

Figure 1

Joining customers to their orders, one customer at a time

customers
customer_id	first_name	city
69	Gary	Chicago
101	Charles	Las Vegas
151	Henry	Orlando

orders
order_id	customer_id	order_date
1231	69	2023-02-06
1734	101	2023-04-28
4230	69	2024-01-08

Result
first_name	order_id	order_date
Gary	1231	2023-02-06
Gary	4230	2024-01-08
Charles	1734	2023-04-28
Henry	NULL	NULL

Step 1 of 6 Two tables that share a column. customer_id is the key in customers and a plain column in orders, pointing back to the customer who placed the order.

Two tables that share a column. customer_id is the key in customers and a plain column in orders, pointing back to the customer who placed the order.
ON c.customer_id = o.customer_id. Take Gary (69) and look for every order whose customer_id is 69. There are two.
Each match becomes one result row. Gary had two matches, so Gary appears twice. His name is copied onto both rows.
Charles (101) has one order, so he gets one row.
Henry (151) signed up but never ordered. No order has customer_id 151. An INNER JOIN keeps only rows with a match, so Henry is simply left out.
A LEFT JOIN keeps every row of the left table. Henry stays, and the columns that would have come from orders have nothing to show: they are NULL.

Why it exists

Harbor & Pine keeps each fact in one place. A customer's name and city live in customers, once. Each order lives in orders, once, with a customer_id saying who placed it. The lines of an order, the products, the returns: each has its own table, linked by ids.

That design keeps the data correct. If Gary moves from Chicago to Denver, one row changes, and every order he ever placed is still his. But almost every real question crosses tables: revenue by category needs order_items and products; "orders per city" needs orders and customers. The JOIN is how you put the pieces back together for one question, without storing them together.

How it works under the hood

Think of the database walking through the left table one row at a time.

Take a row from the left table, say Gary, customer_id 69.
Find its matches in the right table: every row where the ON condition is true. For ON o.customer_id = c.customer_id, that is every order with customer_id 69.
Emit one result row per match. Two orders, two rows. Gary's name and city are copied onto each of them.
Decide about rows with no match. This is the only difference between the join types. An INNER JOIN emits nothing for them. A LEFT JOIN emits the left row once, with NULL in every column that would have come from the right table.

Real databases don't literally loop like this. They build hash tables, sort, and use indexes, so a join of 12,000 orders to 2,000 customers takes milliseconds. The result is exactly what the slow loop would give, though, and the loop is the right picture to hold in your head.

The number of rows is decided by the matches

Step 3 is where most join surprises come from. A row appears in the result once per match, so:

a customer with no orders: 0 rows (INNER) or 1 row with NULLs (LEFT);
a customer with one order: 1 row;
a customer with five orders: 5 rows.

When the right table has many rows per key, the left table's values are repeated. That is fine while you count or sum the right table's columns. It goes wrong the moment you add up a column that belongs to the left table.

Figure 2

One order, three lines: a one-to-many join repeats the order

orders
order_id	channel	shipping_fee
1049	marketplace	4.99

order_items
order_item_id	order_id	quantity
139	1049	2
140	1049	2
141	1049	3

Result of the join
order_id	order_item_id	shipping_fee
1049	139	4.99
1049	140	4.99
1049	141	4.99

SUM(shipping_fee) after the join
shipping_fee
14.97

Step 1 of 4 Order 1049 paid one shipping fee of $4.99. It has three order lines in order_items.

Order 1049 paid one shipping fee of $4.99. It has three order lines in order_items.
Joining on order_id, the single order row matches all three lines.
Three matches, three result rows. The order's columns are copied onto every one of them, shipping_fee included.
Add up shipping_fee now and you get $14.97 for an order that paid $4.99. Nothing is wrong with the join. The total is summing a copy per line.

This is called fan-out. The shipping fee is per order, but after joining to order_items there is one copy per line. Sum the fee per order first and join afterwards, or count with COUNT(DISTINCT o.order_id) when you need orders and not lines.

Where the NULLs come from

A NULL in a LEFT JOIN result means "this row had no match", not "the value is empty in the database". Henry's order_id is NULL because there is no order to take it from. That is useful: WHERE o.order_id IS NULL after a LEFT JOIN is the standard way to find customers who never ordered, products never sold, or sessions that never turned into a purchase.

Checking a join before you trust it

Two quick counts catch almost every join mistake. Count the rows of the left table, then count the rows of the join. Equal counts mean every left row found at most one match. A bigger count means some key repeats on the right side, which is expected for customers to orders and a bug for orders to a table you believed had one row per order. Then count the NULLs in a key column from the right table: that is how many left rows found no match at all.

The order SQL runs in

The NULLs also explain the most common LEFT JOIN bug. SQL runs FROM and the joins first and WHERE afterwards, on the joined rows.

Figure 3

Why a WHERE on the right table undoes a LEFT JOIN

FROM customers c every customer
LEFT JOIN orders o ON … Henry kept, with NULLs
WHERE o.status = 'completed' NULL = 'completed' is not true
SELECT … what is left

Step 1 of 4 The database builds the joined rows first. FROM and the LEFT JOIN run before any filter, and Henry is in there with NULL order columns.

The database builds the joined rows first. FROM and the LEFT JOIN run before any filter, and Henry is in there with NULL order columns.
Then WHERE checks every joined row. For Henry, o.status is NULL, and NULL = 'completed' is unknown, which WHERE treats as false.
Henry's row is gone before SELECT ever sees it. The LEFT JOIN now returns exactly what an INNER JOIN would.
The fix: move the condition into ON: LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'. It then decides which orders match, and every customer stays.

A condition on the right table in WHERE compares the NULLs with a value. The comparison is never true, so the rows the LEFT JOIN kept are thrown away. Put conditions about the right table in ON when you want to keep every left row.

Common misconceptions

"A JOIN puts the two tables side by side"

Rows are paired by the ON condition, never by position. Shuffle orders and the result is the same set of rows. If two tables happen to be in the same order and you expect row 1 to go with row 1, you are thinking of a spreadsheet paste, which is a different (and fragile) thing.

"A LEFT JOIN returns as many rows as the left table"

It returns at least one row per left row. A customer with three orders still produces three rows. Check it on any join you write: count the rows before and after. If the count grew, a key you thought was unique isn't.

"NULL in the result means the data is missing"

In an INNER JOIN result a NULL can only come from the data itself. In a LEFT JOIN result it usually means "no match". Before filling NULLs with 0 or "unknown", ask which one it is: a customer with no orders has zero revenue, which is a real answer, while a missing city is a data problem.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

Customer 151 has no orders. You run

SELECT c.first_name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NOT NULL

. Is customer 151 in the result? Yes, with order_id NULL, because a LEFT JOIN always keeps every customer No: the WHERE runs after the join and removes the row where order_id is NULL Yes, twice, because a LEFT JOIN adds an extra row for customers without orders No: the LEFT JOIN itself drops customers with no match