Python SQL NULL Values: Three-Valued Logic, IS NULL & IS NOT NULL
In SQL, `NULL` represents missing, unknown, or inapplicable data. It is NOT zero, NOT an empty string, and NOT a space. Because NULL represents the unknown, comparing anything with NULL using `=` or `!=` always evaluates to `UNKNOWN`. You MUST use `IS NULL` and `IS NOT NULL`.
"`NULL` is a sealed mystery gift box with unknown contents. If you ask: "Is the mystery box equal to $50?" the answer is not Yes or No, but "I don't know" (UNKNOWN)."
Deep Dive: How It Works
Why `= NULL` Fails: In SQL 3-valued logic (3VL), `NULL = NULL` is UNKNOWN, not TRUE. Because WHERE only retains TRUE rows, `WHERE col = NULL` always returns 0 rows.
Testing Nullability: Always write `WHERE col IS NULL` or `WHERE col IS NOT NULL`.
NULL Propagation: Most arithmetic operations with NULL evaluate to NULL (`10 + NULL -> NULL`). Aggregate functions like `AVG()` and `SUM()` ignore NULL rows.
Syntax Blueprint
SELECT column1 FROM table_name WHERE column1 IS NULL; SELECT column1 FROM table_name WHERE column1 IS NOT NULL;
Always use IS NULL or IS NOT NULL to test for missing data.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Writing `WHERE phone != '555-0100'` and expecting rows with NULL phone numbers to be returned.Why it happens: When phone is NULL, `NULL != '555-0100'` evaluates to UNKNOWN, which WHERE discards.
How to fix: Include null check: `WHERE phone != '555-0100' OR phone IS NULL`.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Find Records with NULL Values
- Select `name` from `reports` where `approved_at IS NULL`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.