Module 1 · Reading one table without fooling yourself
Lesson 2 of 6 · 6 min · English · Read-only
NULL means no value was recorded, so aggregates and comparisons skip it unless you ask for it with IS NULL, and a percentage means something only when its numerator and denominator describe the same stated group of rows.
NULL means there is no value to use, because it is unknown or does not apply. It is not zero and not an empty string. In the orders table, coupon is NULL for every order placed without a code, while a shipping_fee of 0 is a real fact: we charged nothing. NULL is the absence of a fact.
Two rules cover most surprises. First, aggregates skip NULL. Counting a column counts only the rows where it has a value, and SUM and AVG leave NULLs out. The average of 10, NULL and 20 is 15, not the 10 you would get by counting NULL as 0, because the divisor is 2.
Second, comparing anything with NULL gives neither true nor false but unknown, and WHERE keeps only rows where the condition is true. So coupon <> 'SPRING' does not mean “not SPRING”. It means “has a coupon, and the coupon is not SPRING”. To ask for missing values, say IS NULL.
A share is a numerator divided by a denominator, and each is a count of some group of rows. “What share of March orders used a coupon?” has two defensible denominators in our data: all seven March orders, or the five that are still paid. Both are legitimate and they give different answers. Choose the one the question means and write it with the number: “of paid March orders, 40% used a coupon.”
Keep the numerator and denominator on the same group. Counting coupons across all orders and dividing by paid orders mixes two groups, and the result is not a share of anything.
The division itself is a trap too. In SQLite and PostgreSQL, dividing two whole numbers returns a whole number, so 2 / 7 is 0. Multiply by 1.0 first, as the query below does.
The seven March orders, 102 to 108 in order, carry these coupon values: SPRING, NULL, NULL, NULL, SPRING, NULL, NULL. A report needs the March orders that did not use the SPRING coupon, and someone writes WHERE coupon <> 'SPRING'.
Predict before the next step
How many of the seven rows does that filter keep?
Your writing stays on this page only. It is not saved or assessed.
Go through the rows. The two SPRING rows fail the test, as intended. For each of the five NULL rows the database asks whether an unknown value differs from SPRING, and the answer is unknown, not true. WHERE keeps only true, so those five rows go too.
The correct answer is 5, which is 7 minus the 2 SPRING orders. The wrong filter raises no error. It just produces an empty report saying nobody ordered without a coupon.
The fix names the missing case: WHERE coupon IS NULL returns the 5. If other coupon codes existed and you wanted every order that is not SPRING, you would write WHERE coupon IS NULL OR coupon <> 'SPRING'. Check the two filters against each other: 2 orders with SPRING plus 5 without make the 7 March orders.
Think it over
Pick a percentage you report or read regularly and write its numerator and denominator in words. Do they describe the same group of rows, and could a colleague choose a different denominator without being wrong?
Inspect the artifact
sqlLanguage: sql7 annotationsWITH orders(order_id, customer_id, order_ts, status, coupon, shipping_fee) AS ( VALUES (101, 1, '2025-02-28 21:15', 'paid', NULL, 5), (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0), (103, 2, '2025-03-09 14:30', 'paid', NULL, 5), (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5), (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5), (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0), (107, 2, '2025-03-28 12:00', 'paid', NULL, 5), (108, 4, '2025-03-31 18:20', 'paid', NULL, 5), (109, 1, '2025-04-01 08:00', 'paid', NULL, 5)),march AS ( SELECT * FROM orders WHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01')SELECT 'all March orders' AS population, COUNT(*) AS orders, COUNT(coupon) AS with_coupon, SUM(shipping_fee) AS shipping, ROUND(1.0 * COUNT(coupon) / COUNT(*), 3) AS shareFROM marchUNION ALLSELECT 'paid March orders', COUNT(*), COUNT(coupon), SUM(shipping_fee), ROUND(1.0 * COUNT(coupon) / COUNT(*), 3)FROM marchWHERE status = 'paid';population orders with_coupon shipping share ----------------- ------ ----------- -------- ----- all March orders 7 2 25 0.286 paid March orders 5 2 15 0.4
Two populations, two shares: 0.286 of all March orders and 0.4 of the paid ones. Neither is an error; they answer different questions. The paid shipping total of 15 returns in lesson 4.
Artifact content has 32 lines and 7 annotations.