Unit 02.01: Filtering: what WHERE silently excludes

Unit ID: SQL-M02-U02 - Estimated active time: 14-17 minutes Objective: predict which rows a filter removes, including the rows you did not think about.

A filter is a claim about every row

WHERE keeps rows where the condition is TRUE. Not "not false" - true. That distinction is where the surprises live.

Our customers table has 4,812 rows:

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;
-- IN     | 2813
-- GB     |  902
-- AE     |  516
-- NULL   |  300
-- SG     |  281

Now ask for "customers outside India":

SELECT COUNT(*) FROM customers WHERE country <> 'IN';
-- 1699

But 4,812 − 2,813 = 1,999. The query lost 300 customers, and nothing warned you.

Where they went

Those 300 rows have country IS NULL. NULL <> 'IN' does not evaluate to TRUE - it evaluates to NULL, which the filter treats as "do not keep". Module 3 covers the logic in full; the practical point here is that every filter you write silently drops the rows where it cannot decide.

The explicit version:

SELECT COUNT(*)
FROM customers
WHERE country <> 'IN' OR country IS NULL;
-- 1999

Which one is correct depends entirely on the question. "Customers we know are outside India" is 1,699. "Customers not recorded as being in India" is 1,999. Both are defensible; only one matches what was asked.

Worked example

A colleague reports 1,699 international customers. Before publishing, run the completeness check:

SELECT
  COUNT(*)                                  AS all_customers,
  COUNT(country)                            AS with_country,
  COUNT(*) - COUNT(country)                 AS missing_country
FROM customers;
-- 4812 | 4512 | 300

Now you can report the number *and* the caveat: 300 customers (6.2%) have no recorded country and are excluded from the split.

Practice

How many orders belong to customers in India? Then explain what your query does about customers whose country is unknown.

SELECT COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'IN';
-- 679

Takeaway

After writing any filter, ask: which rows can this condition not decide about? Those rows are gone, and your report will not mention them unless you do.

---

Mark complete