IS NULL and IS NOT NULL
Test for missing values correctly.
Testing for Missing Values
Since comparisons with NULL always return unknown, SQL gives you two dedicated operators to test for missing values: IS NULL and IS NOT NULL.
These are the only reliable way to find or exclude NULLs. In this lesson you'll learn to use them correctly.
-- Rows where phone is missing
SELECT name FROM customers WHERE phone IS NULL;
-- Rows where phone is present
SELECT name FROM customers WHERE phone IS NOT NULL;Why = NULL Fails
It's tempting to write WHERE phone = NULL, but it never matches anything. The condition evaluates to unknown for every row, and WHERE keeps only true rows.
The result is an empty set — a silent bug, since no error is raised.
-- Always returns 0 rows, even if NULLs exist
SELECT * FROM customers WHERE phone = NULL;
-- The fix
SELECT * FROM customers WHERE phone IS NULL;All lessons in this course
- What NULL Really Means
- IS NULL and IS NOT NULL
- COALESCE and NULLIF
- NULLs in Aggregates and Joins