How Does INNER JOIN Match Rows?

An INNER JOIN pairs every row on the left with every row on the right that satisfies the join condition, and keeps only the pairs that matched. Rows on either side with no partner are discarded entirely. That last part is what makes it different from the other join types, and it is also why an INNER JOIN can silently reduce a result set.

The condition is usually an equality on a foreign key, but it can be anything that evaluates to TRUE — a range, a comparison, even an expression. What matters is that NULLs are involved: any comparison with a NULL evaluates to UNKNOWN, never TRUE, so a row whose join key is NULL can never be matched by an equality condition.

Two structural details. The order of the FROM clauses does not change the result — A INNER JOIN B and B INNER JOIN A return the same rows with the columns swapped — but it does change how the optimizer reads the query, and it changes which table has to be the driving table. And the join is a set operation on pairs, so if both sides have duplicate keys, the number of output rows is the product of the matching rows on each side.

The last point is the one to watch. Joining a customer with three orders and then aggregating produces three rows for that customer, and a SUM over those rows counts the customer's order values three times. That fan-out is not a bug in the join; it is the correct output of the join, and it becomes a bug in whatever aggregates it afterwards.

A row whose join key is NULL never matches anything in an INNER JOIN, because the equality comparison produces UNKNOWN rather than TRUE. The row is not returned at all — there is no error, and no NULL in the output. It simply vanishes.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, NULL, 60.00);

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

Three rows come back: Ada Lovelace with orders 1 and 2, and Grace Hopper with order 3. Order 4 is missing. It has no customer to belong to, so there is nothing to match it against, and an INNER JOIN has no way to report a row that has no partner.

If those orphaned orders matter, join from the other side with a LEFT JOIN — FROM orders o LEFT JOIN customers c ON c.id = o.customer_id — which keeps all four orders and puts NULL in the name column for order 4. That is the standard fix for finding records whose reference points at nothing, and it is also the shape you want for a data-quality report.

Duplicate keys are the second half of the question. If a customer had two rows in the customers table with the same id, each of its orders would be joined to both, and the output would contain each order twice. The join produces the full cross product of the matching rows on the two sides, so a one-to-many join multiplied by a many-to-one join becomes a many-to-many result. The practical defence is to guarantee uniqueness on the side you expect to be unique, with a primary key or a unique constraint, so the fan-out cannot happen at all.

Functionally, in most cases, there is none. The old-style comma join — FROM customers, orders WHERE customers.id = orders.customer_id — is exactly an INNER JOIN written differently. Both produce the same rows, and modern optimizers recognise the pattern and generate the same plan.

The differences are about clarity and safety rather than results. The comma join can accidentally produce a cross product if you forget the equality condition, and when you do include it, the filter lives in the WHERE clause where it can interact badly with other predicates. With an explicit INNER JOIN, the join condition sits next to the tables it relates, so a reviewer can see at a glance what is being joined and why.

There is one case where the difference is real, and it is worth knowing. When you write FROM a, b, c WHERE a.x = b.x AND b.y = c.y, the filters and the join conditions are interleaved, and a condition that is really a filter on one table can end up evaluating against the product of the others. With explicit INNER JOIN syntax each pair is joined by exactly one condition, so the optimizer has a clear tree of joins to work with, and adding a filter to one table does not accidentally change the join semantics.

The other difference is that the comma join has no way to express an outer join. If you find yourself rewriting a comma join as an INNER JOIN, which you should, the conversion also gives you the option of switching one of them to a LEFT JOIN when the requirement turns out to be that unmatched rows should survive.

The rule of thumb: always use explicit join syntax. It is not a style preference — it is the form that lets the database see the join structure, and the form that stops a stray comma from quietly turning a targeted query into a cross product.