NULL Values

Concept. NULL means "unknown," not 0 or empty. Any comparison with NULL returns UNKNOWN, a third truth value, so a WHERE test drops that row.

Intuition. Leave a listen's rating blank and SQL stores NULL. Ask "is rating > 4?" about an unknown and the answer is UNKNOWN, not yes or no, so WHERE quietly drops the row and only IS NULL finds it.

NULL adds a third truth value, UNKNOWN. Binary tests are TRUE (4.5 > 4.0) or FALSE (4.5 > 5.0), but any comparison with NULL (4.5 > NULL, NULL = NULL, rating = NULL) is UNKNOWN, shown grey with a question mark. WHERE keeps only TRUE rows. FALSE and UNKNOWN rows drop silently; rating IS NULL finds NULL rows.

Figure 1. SQL uses three-valued logic. Comparisons with NULL evaluate to UNKNOWN. WHERE keeps only TRUE rows. FALSE and UNKNOWN rows drop silently.

Two-Valued Logic Refresher

Two-valued boolean logic truth tables. AND: only TRUE AND TRUE is TRUE; any FALSE makes it FALSE. OR: any TRUE makes it TRUE; only FALSE OR FALSE is FALSE. NOT flips TRUE to FALSE and FALSE to TRUE.

Figure 2. Two-valued boolean logic. AND requires TRUE on both sides. OR requires TRUE on at least one side. NOT flips the value.

What Changes When NULL Enters

How AND, OR, and NOT handle UNKNOWN. FALSE AND UNKNOWN is FALSE, while TRUE AND UNKNOWN stays UNKNOWN. TRUE OR UNKNOWN is TRUE, while FALSE OR UNKNOWN stays UNKNOWN. NOT UNKNOWN stays UNKNOWN. Use rating IS NULL when the question is whether a value is missing.

Figure 3. FALSE decides an AND expression, and TRUE decides an OR expression. Otherwise, UNKNOWN remains. Use IS NULL to test whether a value is missing.

NULL with AND/OR Logic

All nine Listens rows under user_id = 3 AND rating > 4 and the corresponding OR predicate. The AND query keeps only listen 8; listen 9 evaluates to UNKNOWN and drops. The OR query keeps seven rows, including listen 9 because user_id = 3 makes the full predicate TRUE. Strikethrough marks dropped rows.

Figure 4. WHERE applies the complete predicate to every row. The AND query keeps only listen 8; listen 9 evaluates to UNKNOWN and drops. The OR query keeps seven rows, including listen 9, because user_id = 3 makes the complete predicate TRUE. A NULL value does not drop a row by itself.

NULL in WHERE Clause

The same NULL row across three WHERE filters, side by side. Query 1 WHERE rating IS NULL keeps row 9 (Daffy, NULL). Query 2 WHERE rating > 4.0 returns 5 rows and Query 3 WHERE rating <= 4.0 returns 3 rows, both without row 9: every comparison with NULL is UNKNOWN, which WHERE drops.

Figure 5. The three filters account for every row: 5 ratings above 4, 3 ratings at or below 4, and 1 NULL. The comparison filters drop row 9 because they evaluate to UNKNOWN. IS NULL finds it.

SELECT listen_id, rating FROM Listens WHERE rating > 4.0;   -- 5 rows
SELECT listen_id, rating FROM Listens WHERE rating <= 4.0;  -- 3 rows
SELECT listen_id, rating FROM Listens WHERE rating IS NULL; -- 1 row

The counts reconcile the table: 5 + 3 + 1 = 9. Without the IS NULL query, row 9 appears to vanish.

NULL in Aggregates

The Listens.rating column has nine values, one of them NULL. COUNT(*) returns 9: it counts every row, including the NULL. COUNT(rating) returns 8: it skips the NULL row. AVG(rating) returns 4.2, the sum of the eight known ratings, 33.6, divided by 8, not 9.

Figure 6. COUNT(*) counts every row, so it returns 9. COUNT(rating) counts non-NULL values, so it returns 8. AVG(rating) divides by that same non-NULL count of 8, not 9.

-- Compare how aggregates handle NULL values
SELECT 
    COUNT(*) AS total_rows,
    COUNT(rating) AS rated_rows,
    AVG(rating) AS avg_rating,
    SUM(rating) AS sum_rating,
    MIN(rating) AS min_rating,
    MAX(rating) AS max_rating
FROM Listens
total_rowsrated_rowsavg_ratingsum_ratingmin_ratingmax_ratingNotes
984.233.62.94.9COUNT(*) counts all; others skip NULL

The eight known ratings sum to 33.6, so AVG(rating) returns 33.6 / 8 = 4.2. SUM, AVG, MIN, MAX, and COUNT(column) skip NULLs; COUNT(*) counts every row.

NULL in DISTINCT

-- Get unique rating values (NULLs treated as one group)
SELECT DISTINCT rating
FROM Listens
ratingAnnotations
NULLNULL forms one unique value
2.9From listen_id 7
3.9Keeping only one (from listen_ids 3, 6)
4.2From listen_id 2
4.5From listen_id 1
4.6From listen_id 5
4.7From listen_id 4
4.9From listen_id 8

DISTINCT treats all NULLs as one unique value, even though NULL = NULL is UNKNOWN. (GROUP BY behaves the same way; covered on the GROUP BY page.)

Common Mistakes

The = NULL Trap

rating = NULL is UNKNOWN on every row, and WHERE keeps only TRUE rows, so the query returns nothing.

-- WRONG: rating = NULL is always UNKNOWN (not TRUE)
SELECT * FROM Listens WHERE rating = NULL

-- CORRECT: Use IS NULL to properly test for NULL
SELECT * FROM Listens WHERE rating IS NULL

The NOT NULL Gotcha

When rating is NULL, rating > 4.0 is UNKNOWN, and NOT UNKNOWN is still UNKNOWN, so WHERE drops the row.

-- WRONG: NOT (rating > 4.0) still excludes NULLs
SELECT * FROM Listens WHERE NOT (rating > 4.0)

-- CORRECT: Explicitly include NULLs if you want them
SELECT * FROM Listens 
WHERE rating <= 4.0 OR rating IS NULL