SQL Quest › SQL Interview Questions › SQL traps › LEFT JOIN with a WHERE filter

SQL LEFT JOIN with a WHERE filter: why it becomes an INNER JOIN

A filter on the right-hand table in WHERE removes the NULL rows a LEFT JOIN keeps, so the LEFT JOIN silently behaves like an INNER JOIN.

The task

List every merchant with its number of open chargebacks, showing 0 where there are none. There are 25 merchants. The data is SQL Quest's synthetic card-transactions set (accounts, merchants, transactions, chargebacks), and every result on this page is what the query returns on it.

The query that looks right

SELECT m.merchant_id,
       COUNT(cb.chargeback_id) AS open_chargebacks
FROM merchants m
LEFT JOIN chargebacks cb ON cb.merchant_id = m.merchant_id
WHERE cb.status = 'open'
GROUP BY m.merchant_id;

Returns: 15 rows

15 rows — ten merchants are missing, and no merchant shows 0.

Why

The LEFT JOIN does keep all 25 merchants: a merchant with no matching chargeback gets one row with NULL in every cb column.

WHERE runs after the join, and NULL = 'open' is not TRUE, so those rows are dropped — along with every merchant whose chargebacks are all closed. What is left is exactly what an INNER JOIN would return.

A condition on the right table belongs in the ON clause: there it decides which rows match, instead of which rows survive.

The fix

Fix 1 — Move the right-table condition into ON

SELECT m.merchant_id,
       COUNT(cb.chargeback_id) AS open_chargebacks
FROM merchants m
LEFT JOIN chargebacks cb
       ON cb.merchant_id = m.merchant_id
      AND cb.status = 'open'
GROUP BY m.merchant_id;

Returns: 25 rows

Fix 2 — Or filter the right table before joining it

SELECT m.merchant_id,
       COUNT(o.chargeback_id) AS open_chargebacks
FROM merchants m
LEFT JOIN (
  SELECT chargeback_id, merchant_id
  FROM chargebacks WHERE status = 'open'
) o ON o.merchant_id = m.merchant_id
GROUP BY m.merchant_id;

Returns: 25 rows

Right answer: All 25 merchants come back; 10 of them with 0 open chargebacks.

Rule of thumb. Conditions on the LEFT table go in WHERE; conditions on the RIGHT table go in ON. A WHERE on a right-table column is only right when you mean to drop the unmatched rows — then write an INNER JOIN.

Read next: SQL joins explained

Where this trap is tested

This trap is question 10 of our Capital One CodeSignal-style mock — a timed practice screen on the same kind of data. The mock asks it in a different form, so this page does not give its answer away.

Practise it

Customers Who Never OrderedMedium · SQL practice questionUsers Who Never Sent MoneyMedium · SQL practice questionDormant Cards — No Transactions in the Last 14 DaysMedium · SQL practice questionAnti-Join Pipeline: Unmatched RecordsHard · SQL practice question

Related SQL traps

NOT IN with NULLwhy the query returns 0 rowsJoin fan-outwhy SUM and COUNT inflate after a JOINBETWEEN on timestampswhy the last day goes missingAverage of averageswhy AVG of group rates is wrong

Which traps would cost you in an interview?

Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.

Take the readiness test