NULL Values: The Unknown in SQL

Bi-Value Logic Refresher

In standard boolean 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

When a value is missing, SQL stores NULL for "unknown." That introduces 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.

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)

Legend: ✓ = TRUE, ✗ = FALSE, ? = NULL

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.

Now look at the other two filters. Where does row 9 go?

5 + 3 + 1 = 9 rows total. Daffy disappears from Queries 2 and 3. Why?

How WHERE handles row 9 across all three filters:

By rule 4, every comparison with NULL is NULL. WHERE keeps only TRUE rows, so NULL is silently dropped. 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 silently 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

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