Lesson 13 of 61

5 mins read

SQL NULL Values: Three-Valued Logic, IS NULL & IS NOT NULL

In Plain English

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

The Mental Model

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

Never Use = NULL: `= NULL` is syntactically allowed in some engines but logically returns 0 records.

Three-Valued Logic (3VL): Conditions evaluate to TRUE, FALSE, or UNKNOWN.

Aggregates Ignore NULL: COUNT(col) counts only non-null values, while COUNT(*) counts all rows.

Common Beginner Traps & How to Fix Them

Mistake: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`.

Pro Tip: In PostgreSQL and modern SQL engines, you can use `IS DISTINCT FROM` (e.g. `a IS DISTINCT FROM b`) for null-safe equality comparison.

Live Interactive Example

Hit Run Code to see it live

Testing and Handling NULL Values

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: IS NULL detected missing phone numbers and IS NOT NULL isolated verified emails.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Find Records with NULL Values

Select `name` from `reports` where `approved_at IS NULL`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.