NULL Values: The Unknown in SQL

Two-Valued Logic Refresher

In two-valued logic, every condition is TRUE or FALSE. Four operators:

• AND: TRUE AND FALSE → FALSE. Both must be TRUE.

• OR: TRUE OR FALSE → TRUE. At least one must be TRUE.

• NOT: NOT TRUE → FALSE. Flips the value.

• = (equality): 4 = 4 → TRUE. Matches values.

SQL Adds NULL: A Third Truth Value

A missing value is stored as NULL, meaning "unknown". That adds a third truth value beyond TRUE and FALSE: UNKNOWN.

(SQL standard: NULL is the value, UNKNOWN is the truth value. Some databases like SQLite also use None/NaN.)

What Changes When NULL Enters

Read each example as a question. 4.5 > NULL → NULL asks "is 4.5 greater than NULL?" The answer is NULL because we don't know NULL's value.

Five patterns: a FALSE forces an AND to FALSE; a TRUE forces an OR to TRUE; NOT of NULL stays NULL; any comparison with NULL is UNKNOWN; only IS NULL gives a definite TRUE or FALSE.

NULL also has its own quirks in aggregates and DISTINCT, covered in their own sections below. GROUP BY and JOINs handle NULL with similar quirks; those live on the GROUP BY and JOINs pages.

NULL in Comparisons

-- Test how NULL behaves with different comparison operators
SELECT 
    listen_id,
    rating,
    rating = 4.5,
    rating > 4.0,
    rating IS NULL
FROM Listens
WHERE listen_id IN (1, 3, 9)

Row 9 demonstrates rule 4: every comparison with NULL evaluates to NULL. To find that row you need IS NULL (rule 5).

NULL with AND/OR Logic

-- Demonstrate three-valued logic with AND/OR operations
SELECT 
    listen_id,
    user_id,
    rating,
    (user_id = 3 AND rating > 4.0) AS and_result,
    (user_id = 3 OR rating > 4.0) AS or_result
FROM Listens
WHERE listen_id IN (1, 2, 9)

Logic Demonstration

• Rows 1 & 2: user_id = 3 is FALSE, rating > 4 is TRUE → AND returns FALSE, OR returns TRUE

• Row 9: TRUE AND NULL → NULL

• Row 9: TRUE OR NULL → TRUE (TRUE dominates in OR)

NULL in WHERE Clause

Query 1: Find the NULL ratings

SELECT listen_id, rating 
FROM Listens 
WHERE rating IS NULL

1 row: Daffy's listen. IS NULL (rule 5) returns a definite TRUE for row 9, so WHERE keeps it.

The other two filters drop row 9.

5 + 3 + 1 = 9 rows total. Daffy's NULL row drops from Queries 2 and 3:

How WHERE handles row 9 across all three filters:

By rule 4, every comparison with NULL is NULL. WHERE keeps only TRUE rows, so the NULL is gone from both. Only IS NULL (rule 5) returns a definite TRUE and finds Daffy.

NULL in Aggregates

-- 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

Calculation Details:

• COUNT(*): All 9 rows in table

• COUNT(rating): Only 8 non-NULL values (row 9 has NULL)

• AVG(rating): (4.5+4.2+3.9+4.7+4.6+3.9+2.9+4.9)/8 = 4.2

• SUM(rating): Sum of 8 non-NULL values = 33.6

• MIN(rating): Minimum non-NULL value = 2.9

• MAX(rating): Maximum non-NULL value = 4.9

Key Points

• COUNT(*) counts every row

• COUNT(column) skips NULLs

• AVG/SUM/MIN/MAX ignore NULLs

• AVG divides by non-NULL count

NULL in DISTINCT

-- Get unique rating values (NULLs treated as one group)
SELECT DISTINCT rating
FROM Listens

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

Common Mistakes

The = NULL Trap

Rule 4 violation (NULL wins). rating = NULL returns NULL on every row, and WHERE drops NULL.

-- WRONG: rating = NULL always returns NULL (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

Rules 3 + 4 violation (NOT NULL stays NULL; comparisons with NULL produce NULL). When rating is NULL, rating > 4.0 is NULL, and NOT NULL is still NULL, so WHERE keeps only TRUE rows and the row is gone.

-- 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

COUNT(*) vs COUNT(column)

Aggregate behavior (separate from the logic rules above). SUM, AVG, MIN, MAX, and COUNT(column) all skip NULLs. Only COUNT(*) counts every row.

-- COUNT(*) vs COUNT(column) behave differently with NULLs
SELECT COUNT(*) AS all_rows,           -- Returns 9
       COUNT(rating) AS rated_rows      -- Returns 8
FROM Listens