IS NULL Operator
SQL · Filtering Rows
Theory
NULL means a value is missing or unknown. It is not zero and not an empty string. Because NULL is unknown, any comparison with it, even NULL = NULL, gives UNKNOWN rather than TRUE, so WHERE manager_id = NULL never matches anything. To test for missing values you must use IS NULL, and IS NOT NULL keeps rows that have a value.
This matters across SQL. Aggregates like SUM and AVG skip NULLs, COUNT(col) counts only non-NULL values while COUNT(*) counts all rows, and arithmetic with NULL returns NULL (salary + NULL is NULL). Use COALESCE to replace NULLs with a default. For example, employees with no manager_id are usually the top of the org chart, and IS NULL finds them.
Why we use it
Real data has gaps: unknown phone numbers, unassigned managers, orders not yet shipped. IS NULL is the only reliable way to find or exclude those rows, since ordinary comparisons silently ignore them.
When to use it
Use IS NULL to find missing values and IS NOT NULL to exclude them. Use COALESCE when you want to substitute a default instead of filtering. Never write = NULL or <> NULL.
How it works
- Read a row
- Look at the tested column
- Is the value NULL? Yes: IS NULL is TRUE: keep the row. No: IS NULL is FALSE: discard it.
- Repeat for every row
- Return rows with missing values
Syntax
SELECT columns FROM table
WHERE column IS NULL;Example
SELECT * FROM employees WHERE manager_id IS NULL;Key points
- NULL cannot be compared with =; use IS NULL / IS NOT NULL
- Represents unknown or missing data, not 0 or ''
- Any arithmetic with NULL returns NULL
- COUNT(col) skips NULLs; COUNT(*) counts every row
- COALESCE(col, default) replaces NULL with a fallback