IS NULL / IS NOT NULL: Checking for Missing Values

NULL in SQL represents missing or unknown data - not zero, not an empty string, but the complete absence of a value. IS NULL and IS NOT NULL are the only correct way to check for it.

Prior lesson

The sample dataset is defined in the Introduction to SQL lesson.

Syntax

-- Check for NULL
SELECT columns
FROM table_name
WHERE column IS NULL;

-- Check for non-NULL
SELECT columns
FROM table_name
WHERE column IS NOT NULL;

Why = NULL doesn't work

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN, not FALSE. Since WHERE only passes rows that evaluate to TRUE, rows with NULL are always silently excluded.

-- This never returns any rows - even if customer_id IS NULL
WHERE customer_id = NULL     -- evaluates to UNKNOWN, not TRUE

-- Correct
WHERE customer_id IS NULL

Examples

Example 1: Find orders with no status recorded

Order 5 in our dataset has a NULL status — the order was placed but no status was assigned yet.

SELECT
    order_id,
    customer_id,
    total_amount,
    status
FROM orders
WHERE status IS NULL;

Result:

order_id	customer_id	total_amount	status
5	4	49.90	NULL

Example 2: Find orders that do have a status

SELECT
    order_id,
    customer_id,
    total_amount,
    status
FROM orders
WHERE status IS NOT NULL;

This returns all orders except order 5.

Example 3: Find customers with no city recorded

Customer 6 (Frank Lee) registered without providing a city — his city column is NULL.

SELECT
    customer_id,
    name,
    city
FROM customers
WHERE city IS NULL;

Result:

customer_id	name	city
6	Frank Lee	NULL

Common Mistake

Never compare to NULL with = or <>

-- These never match anything:
WHERE email = NULL
WHERE email <> NULL

-- Always use:
WHERE email IS NULL
WHERE email IS NOT NULL

Also remember that NULL in an IN list can silently break NOT IN (see the IN lesson).

Practice

Write a query that returns all orders where status is not null, showing order_id, customer_id, and status.

Show answer

SELECT
    order_id,
    customer_id,
    status
FROM orders
WHERE status IS NOT NULL;

Expected result (order 5 is excluded because its status is NULL):

order_id	customer_id	status
1	1	completed
2	1	completed
3	2	completed
4	3	completed
6	1	cancelled
7	2	completed
8	5	completed
9	3	pending
10	4	pending