How does an INNER JOIN decide which rows match, and what happens to rows that find no partner?
An inner join lines up the rows of the two tables using the condition you give it, and it keeps only the rows where the condition is true.
So what happens to the rows that find no partner? They are thrown away. That is the whole point of the inner join, and it is the thing that surprises people when they expect all of the rows.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Ada Lovelace'),
(2, 'Grace Hopper'),
(3, 'Alan Turing');
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, 120.00),
(3, 2, 900.00);
-- Only the customers who have ordered something.
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.amount;
name amount
Ada Lovelace 120.00
Ada Lovelace 250.00
Grace Hopper 900.00
So Alan Turing is not in the answer, because there is no order with his customer number. And Ada Lovelace is in the answer twice, because she has two orders.
That last point is the one that catches people. An inner join produces one row for every matching pair, so if one side has three rows that match and the other has two, you get six rows. The join does not collapse them.
The next two questions in this topic look at the two edge cases around that.