INNER JOIN vs LEFT JOIN: Differences and the WHERE Trap

The short answer: INNER JOIN returns a row only when the join condition finds a match in both tables. LEFT JOIN (the same thing as LEFT OUTER JOIN) returns every row from the left table, matched or not, and where there’s no match it fills the right table’s columns with NULL. So an inner join answers “show me pairs that exist”, and a left join answers “show me everything on this side, plus whatever goes with it”.

That’s what interviewers expect in the first sentence. The questions that follow are the ones that catch people: why a COUNT is off by one per customer, why a WHERE clause made the left join behave like an inner join, and when one is faster than the other.

Side by side

Both joins multiply rows when one left row matches several right rows. A left join never returns fewer rows than the left table has (before any WHERE), and an inner join can return anything from zero upward.

One small dataset, both joins

Three customers, three orders. Chen has never ordered.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       NUMERIC NOT NULL,
  status      TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES (10, 1, 40, 'paid'), (11, 1, 25, 'refunded'), (12, 2, 90, 'paid');

The inner join pairs each order with its customer:

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

name | order_id | total
Asha | 10       | 40
Asha | 11       | 25
Ben  | 12       | 90

Swap INNER for LEFT and Chen appears with nothing next to her name:

name | order_id | total
Asha | 10       | 40
Asha | 11       | 25
Ben  | 12       | 90
Chen | NULL     | NULL

These queries, and every one below, were run in SQLite through Python’s sqlite3 module. PostgreSQL returns the same rows for all of them, since this is standard join behaviour; only the plan output at the end differs by engine.

Notice that Asha appears twice in both results. Joins don’t deduplicate. If a customer has five orders, the join produces five rows for that customer, and any column from customers is repeated five times. Summing a customer column after a one-to-many join is a common way to double-count money.

Counting through a LEFT JOIN

“List every customer with their order count, including zeros” is the textbook reason to reach for a left join. The first attempt usually looks like this:

SELECT c.name, COUNT(*) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

name | orders
Asha | 2
Ben  | 1
Chen | 1

Chen has no orders, yet the count says 1. COUNT(*) counts rows, and the left join gave Chen one row (the NULL-filled one). Count a column from the right table instead, because COUNT(column) skips NULLs. For sums, wrap the result in COALESCE, since SUM over nothing but NULL is NULL, not zero:

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

name | orders | spent
Asha | 2      | 65
Ben  | 1      | 90
Chen | 0      | 0

Pick the primary key (o.id) or another NOT NULL column to count. Counting a nullable column like o.coupon_code would also skip real orders that happen to have no coupon.

The WHERE filter that undoes the LEFT JOIN

Now the report should show only paid orders, and still list every customer. Adding the condition to WHERE looks natural:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

name | order_id
Asha | 10
Ben  | 12

Chen is gone. The join did produce her NULL row, but WHERE runs afterwards, and NULL = 'paid' evaluates to unknown, which WHERE treats as false. Any test on a right-table column that can’t be true for NULL throws away exactly the rows the left join was there to keep. The result is identical to an inner join, and the planner knows it: SQLite’s EXPLAIN QUERY PLAN for this query scans orders first, an order it’s only allowed to choose for an inner join. PostgreSQL’s EXPLAIN shows the same reduction as a plain Hash Join or Nested Loop instead of a Left Join node.

The fix is to make the condition part of the match, so it decides which orders attach, not which output rows survive:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

name | order_id
Asha | 10
Ben  | 12
Chen | NULL

The rule of thumb: conditions on the optional (right) table go in ON; conditions on the preserved (left) table go in WHERE. For an inner join it doesn’t matter where you put a condition, which is exactly why habits formed on inner joins break left joins. There’s a longer treatment of this, including the OR o.id IS NULL non-fix, in SQL left joins and filter placement.

Finding rows with no match

The NULLs a left join produces are useful on purpose. Keep only the unmatched rows and you have an anti-join, “customers who never ordered”:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
Chen

Test a column that can’t be NULL in a real match, like the right table’s primary key or the join column itself. NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) answers the same question, and many people find it reads more clearly. PostgreSQL usually plans both as the same anti-join. Avoid NOT IN (SELECT customer_id FROM orders): if any customer_id is NULL, it returns no rows at all, because of three-valued logic.

Performance: is one faster?

Interviewers sometimes ask “which is faster?” hoping you’ll say inner. The honest answer is that they compute different results, so pick the one that’s correct, then tune. In practice an inner join gives the optimizer more freedom: it can reorder inner joins any way it likes and start from whichever table is cheaper. A left join pins the left table as preserved, which limits reordering. When a left join’s right side is never used and is guaranteed to match at most one row (joined on a unique key), PostgreSQL can remove the join entirely, which an inner join can’t get away with, since an inner join could still drop rows.

Don’t “optimise” a left join into an inner join unless the data guarantees a match, such as a NOT NULL foreign key. Otherwise the speed-up comes from silently losing rows.

Interview practice: three answers to one question

A manager asks “how many customers have placed an order?” on the dataset above. One colleague writes SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id and gets 3. Another switches to LEFT JOIN and gets 4. Explain both numbers and give the right query.

Answer

The real answer is 2 (Asha and Ben). The inner join returns one row per matching order, and Asha has two orders, so COUNT(*) counts 3 order rows, not customers. The left join keeps those three rows and adds Chen’s NULL row, giving 4: it answers “rows in the joined result”, a number with no business meaning here.

Either count distinct customers from the inner join, or ask the question directly with EXISTS, which never multiplies rows:

SELECT COUNT(DISTINCT c.id) FROM customers c JOIN orders o ON o.customer_id = c.id;   -- 2

SELECT COUNT(*) FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);                     -- 2

A strong answer also names the underlying lesson: decide the grain of the result (one row per customer) before choosing the join, and check row counts after every join you add.

Where to go next

• SQL interview questions and the SQL multiple-choice quiz
• SQL joins and unexpected row multiplication
• SQL count star versus count column
• SQL null and three-valued logic
• PostgreSQL documentation: joined tables