Four Queries, One Wrong Assumption: NULL Semantics in a Shipment Report
Priya is a junior analyst on a three-person data team at a freight-forwarding company. The production database is PostgreSQL 15 and the shipments table holds 480,000 rows. The column delivered_at is a nullable timestamp: it is filled in when a parcel reaches the customer and stays NULL until then. The column carrier_id is also nullable, because some legacy rows were imported before carriers were recorded. The team is preparing a service-level report, and Priya runs four queries whose results she cannot explain. (1) SELECT COUNT(*) FROM shipments returns 480,000 while SELECT COUNT(delivered_at) FROM shipments returns 391,204. (2) SELECT * FROM shipments WHERE delivered_at = NULL returns zero rows. (3) SELECT * FROM shipments WHERE delivered_at <> DATE '2024-05-01' returns 3,102 rows, but she expects 88,796. (4) SELECT * FROM shipments WHERE carrier_id NOT IN (SELECT carrier_id FROM carriers WHERE active = false) returns zero rows, although 61 carriers are inactive and thousands of shipments were handled by active ones. Her team lead insists the data is correct and the queries are wrong.
1. Explain the difference between the two COUNT results and why queries (2) and (3) return what they return. Give corrected SQL for both.
Read the worked answer
COUNT(*) versus COUNT(column). COUNT(*) counts rows; COUNT(expr) counts only the rows where that expression is not NULL. So 480,000 − 391,204 = 88,796 shipments are undelivered. Nothing is missing from the table.
Why (2) returns nothing. NULL is not a value, it marks an unknown or not-applicable state. Standard SQL uses three-valued logic: comparisons involving NULL evaluate to UNKNOWN, never to TRUE. delivered_at = NULL is UNKNOWN for every row, and WHERE keeps only rows that are TRUE, so the result is an empty set. The same applies to <>, <, IN and even NULL = NULL.
Why (3) is short. Rows with a NULL delivered_at produce UNKNOWN and are dropped, so only the 3,102 genuinely comparable rows remain. The 85,694 undelivered rows are silently excluded.
-- (2) corrected
SELECT COUNT(*) FROM shipments WHERE delivered_at IS NULL; -- 88,796
-- (3) corrected: include the undelivered rows explicitly
SELECT * FROM shipments
WHERE delivered_at IS NULL
OR delivered_at <> DATE '2024-05-01';
-- PostgreSQL shorthand with identical meaning
SELECT * FROM shipments
WHERE delivered_at IS DISTINCT FROM DATE '2024-05-01';
General rule to carry into the report: aggregates ignore NULLs, so AVG, SUM and COUNT(col) describe only the non-NULL subset and must not be read as covering the whole table.
2. Query (4) returns zero rows. Explain the mechanism and give two correct rewrites with different trade-offs.
Read the worked answer
Mechanism. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last term is UNKNOWN for every x, and TRUE AND UNKNOWN is UNKNOWN, so no row can ever satisfy the predicate. The subquery on carriers returned at least one row with a NULL carrier_id - very likely one of the legacy carriers - which poisoned the whole list. NOT IN is not simply the negation of IN when NULLs are present; IN still works with NULLs in the list (a match returns TRUE), but NOT IN collapses to no rows.
Rewrite A - correlated NOT EXISTS (preferred).
SELECT s.*
FROM shipments s
WHERE NOT EXISTS (
SELECT 1 FROM carriers c
WHERE c.carrier_id = s.carrier_id
AND c.active = false
);
EXISTS is two-valued: it returns TRUE or FALSE, never UNKNOWN, so NULLs cannot break it. Anti-joins are also optimisable to a hash anti-join, so this scales well.
Rewrite B - defend the subquery.
SELECT * FROM shipments
WHERE carrier_id NOT IN (
SELECT carrier_id FROM carriers
WHERE active = false AND carrier_id IS NOT NULL
);
Cheaper to read, keeps the NOT IN shape the team already knows, and runs well on small lists - but it is fragile: the IS NOT NULL guard must be repeated every time the subquery is edited.
Note the behaviour difference on the third group: shipments with a NULL carrier_id are excluded by both rewrites. If those rows matter to the report, they need their own branch (OR carrier_id IS NULL) and a decision about what 'inactive carrier' means for an unknown carrier.
3. Priya wants the most recently delivered shipments first with the still-undelivered ones at the very end of the list. She writes `ORDER BY delivered_at DESC` on PostgreSQL. What does she actually get, and how should the statement be written to stay portable?
Read the worked answer
What she gets. In PostgreSQL, DESC defaults to NULLS FIRST, so the 88,796 undelivered shipments appear at the top of the result, not the end. This is the opposite of her intent, and it is the same class of trap as the WHERE clauses: a NULL row is not a large value and not a small value, it is unordered by default and the DBMS decides where to put it.
Fix on PostgreSQL.
SELECT shipment_id, delivered_at
FROM shipments
ORDER BY delivered_at DESC NULLS LAST;
Why 'portable' matters here. Null ordering defaults differ between products: Oracle and PostgreSQL put NULLs last on ASC; MySQL and MariaDB treat NULL as lowest, so it sorts last on DESC; SQL Server treats NULL as lowest too. A script moved between platforms silently changes its first page. Announce the intent explicitly:
ORDER BY delivered_at DESC NULLS LAST; -- standard SQL, supported by PostgreSQL, Oracle, MySQL 8
Portable fallback when the target DBMS lacks the clause - sort on a computed flag first:
ORDER BY (delivered_at IS NULL), delivered_at DESC;
The flag is 0 for delivered rows and 1 for NULLs, so delivered rows sort first and the NULL block stays stable at the bottom. Ordering is part of the report contract, so it belongs in the query, not in a comment.