Why Two SQL Queries Return Different Row Counts

Cause 1: Join fan-out from duplicate keys

-- If customer_id isn't unique in "addresses" (e.g. one customer, two addresses),
-- this join multiplies the customer's row for every matching address row
SELECT c.customer_id, c.name, a.city
FROM customers c
JOIN addresses a ON c.customer_id = a.customer_id;

-- Find exactly which keys will fan out before joining
SELECT customer_id, COUNT(*) AS row_count
FROM addresses
GROUP BY customer_id
HAVING COUNT(*) > 1;

Fan-out isn't a bug in the join itself — the join is doing exactly what it's told. The real question is whether the join key is expected to be unique, and if it isn't, whether the query needs to aggregate or deduplicate before joining.

Cause 2: WHERE vs JOIN condition placement

-- Condition in ON: preserves every customer, even those with no active order
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'active';

-- Same condition in WHERE: silently converts this back into an INNER JOIN,
-- dropping every customer who has no active order at all
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'active';

The second version looks almost identical but returns fewer rows — WHERE is applied after the join completes, and any row with a NULL o.status (from an unmatched LEFT JOIN row) fails the = 'active' comparison and gets removed.

Cause 3: Implicit NULL exclusion

-- This looks like it should return every row where status isn't 'cancelled' --
-- but rows where status IS NULL are excluded too, since NULL != 'cancelled'
-- never evaluates to TRUE
SELECT * FROM orders WHERE status != 'cancelled';

-- Explicitly include NULLs if that's the intent
SELECT * FROM orders WHERE status != 'cancelled' OR status IS NULL;

A debugging checklist

1. Remove all JOINs and WHERE conditions, confirm the base table's row count matches expectations.
2. Add back one JOIN at a time, checking row count after each — a jump signals fan-out from that specific join.
3. Add back one WHERE condition at a time, checking row count after each — a drop signals either a NULL-exclusion issue or a misplaced filter that should have been in ON.
4. For any JOIN column suspected of fan-out, run the GROUP BY / HAVING COUNT(*) > 1 check on it directly.

Real-world example: a "missing" 200 customers

A customer list report shows 9,800 customers, but the customers table has 10,000 rows. Walking the checklist: removing all joins and filters confirms the base table returns 10,000. Adding back a LEFT JOIN to a loyalty_status table keeps the count at 10,000. Adding back WHERE loyalty_status.tier != 'inactive' drops the count to 9,800 — the 200 missing customers are exactly the ones with no loyalty_status row at all, whose joined tier is NULL and fails the != comparison. The fix is either OR tier IS NULL or moving the condition into the ON clause, depending on whether those 200 customers should be included.

Common mistakes

• Assuming a join key is unique without verifying it, especially after a schema change on the other table.
• Treating != as "everything except this value" without accounting for NULL.
• Debugging the whole query at once instead of isolating clauses one at a time.
• Not distinguishing an INNER JOIN written as a LEFT JOIN with a WHERE filter from an intentional LEFT JOIN.