Not every "no value here" in a real database is an actual NULL. Older systems, spreadsheet imports, and quick internal tools frequently invent a placeholder instead — -1 for "not rated yet," 'N/A' for "not applicable," 9999 for "unknown date." To every SQL aggregate function, a placeholder like -1 is just a normal, valid integer. AVG() has no way to know it's standing in for "nothing" — it averages it in like any other rating, and the result quietly skews toward whatever the placeholder happens to be.
Verified by actually running this against SQLite — not hand-computed. A ride-hailing app's trip_ratings table, where riders rate 1–5, but a trip the rider never bothered to rate is logged with a legacy sentinel value of -1 instead of a proper NULL.
CREATE TABLE trip_ratings(
trip_id INTEGER,
driver_name TEXT,
rating INTEGER
);
INSERT INTO trip_ratings VALUES
(1,'Arjun',5),
(2,'Arjun',4),
(3,'Arjun',-1),
(4,'Arjun',5),
(5,'Arjun',-1),
(6,'Meena',3),
(7,'Meena',3),
(8,'Meena',4);| trip_id | driver_name | rating |
|---|---|---|
| 1 | Arjun | 5 |
| 2 | Arjun | 4 |
| 3 | Arjun | -1 |
| 4 | Arjun | 5 |
| 5 | Arjun | -1 |
| 6 | Meena | 3 |
| 7 | Meena | 3 |
| 8 | Meena | 4 |
Arjun actually earned three real ratings — 5, 4, 5 — and two of his five trips were simply never rated. Meena has three trips, all rated, no placeholders at all.
"Average rating per driver" reaches for the obvious aggregate:
SELECT driver_name, ROUND(AVG(rating), 2) AS avg_rating_bug
FROM trip_ratings
GROUP BY driver_name;| driver_name | avg_rating_bug |
|---|---|
| Arjun | 2.4 |
| Meena | 3.33 |
Arjun — who earned a genuine 5, 4, and 5 on every trip he was actually rated on — comes out with a worse average than Meena, who never scored higher than a 4. The two unrated trips, logged as -1, dragged his real 4.67 average all the way down to 2.4. Nothing errored. The number is just wrong, and it's wrong in exactly the direction that makes a good driver look bad.
AVG() sums every non-NULL value in the group and divides by how many there are. -1 is not NULL — it's a completely ordinary integer as far as SQL is concerned, so it gets summed in right alongside the real 4s and 5s and counted toward the denominator too. The bug isn't in AVG(); it's that the data uses a number to mean "absence," and nothing in the query told SQL that -1 means anything other than -1.
SELECT driver_name,
ROUND(AVG(NULLIF(rating, -1)), 2) AS avg_rating,
COUNT(NULLIF(rating, -1)) AS rated_trips,
COUNT(*) AS total_trips
FROM trip_ratings
GROUP BY driver_name;| driver_name | avg_rating | rated_trips | total_trips |
|---|---|---|---|
| Arjun | 4.67 | 3 | 5 |
| Meena | 3.33 | 3 | 3 |
NULLIF(a, b) returns NULL if a equals b, and returns a unchanged otherwise. NULLIF(rating, -1) turns every -1 into a genuine NULL before AVG() ever sees it, and a real NULL is exactly what every aggregate function already knows how to skip. Arjun's average corrects itself to 4.67 — his actual rating on the trips people bothered to rate — and COUNT(NULLIF(rating, -1)) reuses the same trick to report that only 3 of his 5 trips were rated at all, a number worth knowing on its own. Meena, who has no -1 rows, is completely unaffected — NULLIF only ever touches the exact value it's told to catch.
NULLIF's single most common real-world use isn't sentinel values at all — it's protecting a division from a zero denominator. orders / clicks is a completely ordinary conversion-rate calculation, right up until a brand-new campaign with zero clicks recorded so far hits that line. SQLite happens to evaluate 5 / 0 as NULL rather than raising an error — but that's a SQLite-specific kindness, not something to depend on. PostgreSQL raises a hard division by zero error and aborts the whole statement; other engines vary. orders / NULLIF(clicks, 0) sidesteps the question entirely: the moment clicks is exactly 0, NULLIF turns it into NULL first, and dividing anything by NULL is always, unconditionally, just NULL — on every engine, with no error, no matter how the database in question happens to feel about zero denominators.
| Use case | Pattern | What it buys you |
|---|---|---|
| Legacy sentinel value | AVG(NULLIF(col, -1)) | Placeholder gets excluded from aggregates like a real NULL would be |
| Safe division | numerator / NULLIF(denominator, 0) | Zero denominator becomes NULL instead of an engine-dependent error or surprise |
| Count only "real" values | COUNT(NULLIF(col, sentinel)) | Reuses COUNT's built-in NULL-skipping instead of a separate WHERE/CASE |
This bug hides especially well because the placeholder value is usually chosen to look plausible — -1, 0, 9999 — never something that would visibly stand out in a spot check of the raw table. The only reliable defense is knowing, for every nullable-in-spirit column, whether "no value" is actually stored as NULL or as a stand-in number wearing NULL's job. Once that's known, NULLIF is a one-line fix either way.