Unit 01.03: NULL is not zero, and not empty

Unit ID: SQL-M01-U04 - Estimated active time: 13-16 minutes Objective: predict how NULL behaves in filters, aggregates, and joins, and handle it deliberately.

NULL means "unknown"

Not zero. Not empty string. Unknown. Once you internalise that, the strange behaviour becomes logical.

SELECT NULL = NULL;    -- NULL  (is one unknown equal to another unknown? unknown)
SELECT NULL = 0;       -- NULL
SELECT NULL <> 5;      -- NULL

Because comparisons with NULL yield NULL (not TRUE), rows with NULL fail every ordinary filter:

-- Customers whose country is not India.
SELECT COUNT(*) FROM customers WHERE country <> 'IN';

This silently excludes every customer whose country is NULL. If 300 of your 4,812 customers have no recorded country, they vanish from the report, and nothing warns you.

Correct, explicit version:

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

NULL in aggregates

Aggregates skip NULLs - which is usually helpful and occasionally misleading:

-- 10 rows, 2 have NULL score
SELECT COUNT(*)      FROM tests;   -- 10  (counts rows)
SELECT COUNT(score)  FROM tests;   -- 8   (counts non-null scores)
SELECT AVG(score)    FROM tests;   -- average of the 8, not the 10

AVG over 8 values when your reader assumes 10 is a real reporting error. If a missing score should count as zero, say so explicitly:

SELECT AVG(COALESCE(score, 0)) FROM tests;

Whether that is correct is a judgement about the data, not about SQL. An unsat test is arguably a zero; an unrecorded one is arguably unknown and should stay excluded. Decide, then document the decision.

NULL from LEFT JOIN

A LEFT JOIN manufactures NULLs for non-matching rows - this is the normal way to find missing things:

-- Customers who have never ordered
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Watch the trap: putting that condition in the ON clause instead of WHERE changes the meaning entirely and returns every customer.

Practice

Table feedback(id, learner_id, rating) has 500 rows; 60 have rating IS NULL.

1. What does SELECT COUNT(rating) FROM feedback; return?
2. Does WHERE rating < 3 include the 60 NULL rows?
3. You must report "average rating". What must you tell the reader?

Takeaway

Every filter you write silently drops NULL rows. Decide whether that is what you want each time, and tell your reader what the denominator actually was.

---

Mark complete