IS NULL / IS NOT NULL

Retrieves rows where the value is NULL.

SELECT column_name FROM table_name WHERE column_name IS NULL;

Retrieves rows where the value is not NULL.

SELECT column_name FROM table_name WHERE column_name IS NOT NULL;

Syntax	Description
IS NULL	Retrieves rows where the column value is NULL. NULL is a special state indicating that a value is unset or unknown.
IS NOT NULL	Retrieves rows where the column value is not NULL.

The following 'employees' table is used in the examples below.

Retrieves employees with no retirement date set (NULL) — i.e., current employees.

sample_is_null.sql

SELECT name, hire_date FROM employees WHERE retire_date IS NULL;

+---------------+------------+
| name          | hire_date  |
+---------------+------------+
| Ayanami Rei   | 2018-04-01 |
| Ikari Shinji  | 2020-07-15 |
| Nagisa Kaworu | 2022-01-10 |
+---------------+------------+
3 rows in set

Retrieves employees who have a retirement date set — i.e., former employees.

sample_is_null.sql

SELECT name, hire_date, retire_date FROM employees WHERE retire_date IS NOT NULL;

+-------------+------------+-------------+
| name        | hire_date  | retire_date |
+-------------+------------+-------------+
| Soryu Asuka | 2015-09-01 | 2023-03-31  |
+-------------+------------+-------------+
1 row in set

Finds employees with no bonus assigned.

sample_is_null.sql

SELECT name FROM employees WHERE bonus IS NULL;

+---------------+
| name          |
+---------------+
| Nagisa Kaworu |
| Soryu Asuka   |
+---------------+
2 rows in set

Note the behavior difference when aggregating columns that contain NULL. COUNT(*) counts all rows including NULLs, while COUNT(column) excludes NULLs.

sample_is_null.sql

SELECT COUNT(*) AS total_count, COUNT(bonus) AS bonus_count FROM employees;

+-------------+-------------+
| total_count | bonus_count |
+-------------+-------------+
|           4 |           2 |
+-------------+-------------+
1 row in set

Example using IFNULL (MySQL) to substitute a default value when the column is NULL.

sample_is_null.sql

SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;

+---------------+-------+
| name          | bonus |
+---------------+-------+
| Ayanami Rei   | 50000 |
| Ikari Shinji  | 30000 |
| Nagisa Kaworu |     0 |
| Soryu Asuka   |     0 |
+---------------+-------+
4 rows in set

The IS NULL and IS NOT NULL syntax works consistently across all major databases.

SELECT name, hire_date FROM employees WHERE retire_date IS NULL;

The function used to replace NULL with a fallback value differs by database. The standard SQL COALESCE function works across all databases.

-- Standard SQL (works in all databases)
SELECT name, COALESCE(bonus, 0) AS bonus FROM employees;

MySQL provides the proprietary IFNULL function, and Oracle provides NVL.

-- MySQL only
SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;

-- Oracle only
SELECT name, NVL(bonus, 0) AS bonus FROM employees;

In SQL, NULL represents a special state meaning "no value exists" or "unknown." NULL is neither 0 nor an empty string, so evaluating it with standard comparison operators (=, <>, etc.) always returns FALSE.

Using WHERE column = NULL will not retrieve NULL rows. Always use IS NULL instead. Similarly, WHERE column != NULL does not work as expected — use IS NOT NULL.

Arithmetic operations involving NULL (for example, salary + bonus when bonus is NULL) also return NULL. To substitute NULL with a value before calculating, use IFNULL(column, fallback) in MySQL or COALESCE(column, fallback) in standard SQL.