SQL / JOINS
INNER JOIN and the rows that disappear
Predict which rows an INNER JOIN drops, recognize NULL and orphan keys as the cause, and audit the loss with NOT EXISTS.
What you will learn
- Read INNER JOIN as a filter over row pairs: keep the pair only when ON is TRUE
- Predict a join's row count from the key overlap, not from either table's size
- Explain why a NULL join key matches nothing, not even a NULL on the other side
- Use NOT EXISTS in both directions to name the rows a join discarded
Understanding INNER JOIN and the rows that disappear
An INNER JOIN does not bolt extra columns onto a table; it builds pairs. Conceptually the engine considers each combination of one customers row with one orders row, evaluates the ON condition for that pair, and keeps the pair only when the condition comes back TRUE. That is why survival has nothing to do with the quality of the row itself: customer 3 is perfectly good data, but with no order to pair with, no pair is ever TRUE and Linus never reaches the result set.
Rows go missing for two different reasons, and it pays to tell them apart. Order 1005 points at customer 9, who does not exist, so no pair involving it can be TRUE; that is an orphan key. Order 1004 has customer_id NULL, and NULL = 1 is not FALSE but UNKNOWN, and ON keeps only TRUE, so UNKNOWN is discarded just as firmly. Neither case raises an error: the query succeeds and quietly returns fewer rows.
That silence is the real hazard. Four customers and five orders became three result rows, and a revenue total computed over the join falls from 360 to 255 with nothing to flag it. The result's row count is a weak alarm, because Ada's two orders contribute two rows and can mask the customers that vanished, so check the key overlap in both directions and compare an aggregate computed before and after the join.
-- customers 3 and 4 have no orders; orders 1004 and 1005 have no valid customer
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER, -- NULL means guest checkout; no FK constraint here
amount INTEGER NOT NULL
);
INSERT INTO customers (customer_id, name) VALUES
(1, 'Ada'), (2, 'Grace'), (3, 'Linus'), (4, 'Alan');
INSERT INTO orders (order_id, customer_id, amount) VALUES
(1001, 1, 120),
(1002, 1, 45),
(1003, 2, 90),
(1004, NULL, 30),
(1005, 9, 75);
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;An INNER JOIN keeps a pair of rows only when the ON condition evaluates to TRUE, so unmatched rows and NULL-keyed rows are dropped silently from both sides.
Worked examples
Name the customers the join threw away
An anti-join returns exactly the customers side of what the INNER JOIN discarded.
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;Example explained
Line 1NOT EXISTS keeps a customer only when the subquery finds no matching order, which is the exact complement of what the INNER JOIN kept.
Line 2Linus and Alan were never wrong or malformed; they simply had no partner row, so the join had no TRUE pair to emit for them.
Line 3Swapping the roles of the two tables in this query lists the discarded orders instead: 1004, whose key is NULL, and 1005, whose customer does not exist.
Line 4Four customers and five orders produced three rows, and this query turns that vague shortfall into two named rows you can act on.
Two NULL keys still do not match
Shows that a NULL join key fails to match even when the other table also has a NULL in the key column.
CREATE TABLE shipments (id INTEGER, region_code TEXT);
CREATE TABLE regions (region_code TEXT, region_name TEXT);
INSERT INTO shipments (id, region_code) VALUES (1, 'EU'), (2, NULL);
INSERT INTO regions (region_code, region_name) VALUES ('EU', 'Europe'), (NULL, 'Unassigned');
SELECT s.id, r.region_name
FROM shipments s
INNER JOIN regions r ON s.region_code = r.region_code;Example explained
Line 1For shipment 1 the condition 'EU' = 'EU' is TRUE, so that pair is emitted.
Line 2For shipment 2 paired with the Unassigned region, NULL = NULL evaluates to UNKNOWN, because NULL stands for a missing value and two missing values are not evidence of equality.
Line 3ON emits a pair only on TRUE, so UNKNOWN is treated the same as FALSE and shipment 2 disappears.
Line 4No amount of extra rows in regions can rescue shipment 2; only replacing its NULL region_code with a real value will.
Watch money vanish from an aggregate
Compares a total over the raw table with the same total computed over the joined rows.
SELECT (SELECT SUM(amount) FROM orders) AS all_orders,
(SELECT SUM(o.amount)
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id) AS joined_orders;Example explained
Line 1The first scalar subquery totals every order: 120 + 45 + 90 + 30 + 75 = 360.
Line 2The second totals only the pairs that survived the join: 120 + 45 + 90 = 255.
Line 3The 105 difference is the guest order (30) plus the orphan order (75), removed with no error and no warning.
Line 4Running both totals side by side is the cheapest check that a join has not changed the meaning of an aggregate.
Important notes
A foreign key on orders.customer_id would have rejected order 1005, but it would still permit order 1004, because foreign keys allow NULL; a fully constrained schema can still lose rows to an INNER JOIN.
JOIN with no preceding keyword means INNER JOIN; writing INNER changes nothing about the result and only signals to the reader that dropping unmatched rows is intended.
Common mistakes
Reading 'customers INNER JOIN orders' as 'customers, plus some order columns': Linus and Alan are gone, so a customer list that should show four names shows two, and nothing in the output signals it.
Assuming two NULL keys match because they look identical: order 1004 drops out and the revenue total falls from 360 to 255 while still looking like a plausible figure.
Treating the joined row count as proof nothing was lost: Ada's two orders produce two rows, so the count can equal or exceed the number of customers even though two customers vanished.
Try it yourself
Change, predict, then run
Using the customers and orders tables above, insert a fifth customer with no orders and a sixth order with customer_id 42. Write down the row count you expect from the INNER JOIN before running it, then confirm with NOT EXISTS in both directions which rows fell out.
Open the SQL workspaceCheck your understanding
employees has 10 rows with a unique emp_id. timesheets has 4 rows: three carry an emp_id that exists in employees, one has emp_id set to NULL. How many rows does 'FROM employees e INNER JOIN timesheets t ON t.emp_id = e.emp_id' return?
- 3, because the NULL emp_id row can never make the ON condition TRUE
- 4, because every timesheet row keeps its place and only employees without timesheets are lost
- 10, because the join returns one row per employee
- 40, because every employee is paired with every timesheet
Show answer
Each of the three timesheets with a real emp_id matches exactly one employee, giving three rows; the fourth timesheet compares NULL to every emp_id, which yields UNKNOWN rather than TRUE, so it is dropped. Option 4 is tempting because people assume loss only happens on the side without matches, but an INNER JOIN discards from both sides, and a NULL key guarantees discard.