NULL Values: The Unknown in SQL

Core Concept

• Example: NULL comparisons are tricky - "rating = NULL" finds nothing while "rating IS NULL" works correctly

NULL is SQL's way of saying "I don't know." Not zero, not empty - genuinely unknown.

Three-Valued Logic Foundation

• Bi-value logic uses TRUE and FALSE.

• SQL uses tri-value logic. NULL creates a third truth value: UNKNOWN. (Note: While NULL and UNKNOWN are the SQL standards, some databases like SQLite also use None/NaN for NULL and may vary in representing UNKNOWN)

• When NULL meets any comparison operator (=, >, <, !=), the result is UNKNOWN.

• Only IS NULL and IS NOT NULL can definitively test for NULL.

Truth Tables for Three-Valued Logic

Key Rules

1. NULL Propagation: Any operation with NULL returns NULL (except IS NULL/IS NOT NULL)

2. WHERE Filtering: Only TRUE conditions pass; UNKNOWN filtered out like FALSE ⚠️

3. Aggregates: Most functions ignore NULL (except COUNT(*) because it's UNKNOWN) ⚠️

4. DISTINCT/GROUP BY: NULL values are considered equal to each other

5. JOIN Behavior: NULL values don't match in joins (even NULL ≠ NULL)

NULL in Comparisons

-- Test how NULL behaves with different comparison operators
SELECT 
    listen_id,
    rating,
    rating = 4.5 AS equals_check,
    rating > 4.0 AS greater_check,
    rating IS NULL AS is_null_check
FROM Listens
ORDER BY listen_id

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

bugfix: sept24. column headers fixed to match AS aliases

• NULL = NULL returns UNKNOWN (not TRUE!)

• Only IS NULL can test for NULL

• UNKNOWN: SQL's third truth value

NULL with AND/OR Logic

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

Logic Demonstration

• Row 1 & 2: Both conditions TRUE → AND returns TRUE, OR returns TRUE

• Row 9: FALSE AND UNKNOWN = FALSE (FALSE dominates in AND)

• Row 9: FALSE OR UNKNOWN = UNKNOWN → returns as NULL in SQL

NULL in WHERE Clause

SELECT listen_id, rating 
FROM Listens 
WHERE rating > 4.0

SELECT listen_id, rating 
FROM Listens 
WHERE rating <= 4.0

SELECT listen_id, rating 
FROM Listens 
WHERE rating IS NULL

Addn: sept24. added output tables to show each query separately

Debug: How WHERE Evaluates Each Row to Produce Above Tables

Missing row alert: Row 9 (NULL) excluded from both Query 1 and Query 2!

Key Points

• WHERE's rule: Only TRUE passes

• NULL rows vanish (UNKNOWN = filtered out)

• Use IS NULL to find them

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

Addn: sept24. added table to show aggregate results as columns

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 and GROUP BY

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

-- Count occurrences of each rating (NULLs form one group)
SELECT rating, COUNT(*) AS COUNT
FROM Listens
GROUP BY rating

Addn: sept24. Simplified table to show only relevant columns

Key Points

• DISTINCT: all NULLs are equal

• GROUP BY: NULLs form one group

• ORDER BY: NULLs go first or last

Common Patterns

• NULL Checks: Use IS NULL or IS NOT NULL, never = NULL

• Aggregate Safety: COUNT(*) counts all rows, COUNT(column) skips NULLs

• Join Behavior: NULL values don't match in joins

• Sorting: NULLs typically sort first or last (database-dependent)

Common Mistakes

The = NULL Trap

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

Problem: NULL = NULL returns UNKNOWN, not TRUE

The NOT NULL Gotcha

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

Problem: NOT UNKNOWN = UNKNOWN, still filtered out

COUNT(*) vs COUNT(column)

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

Problem: COUNT(column) skips NULL values