MySQL IS NULL and IS NOT NULL: Filter Missing Values

NULL means “unknown or missing” (NULL). Filtering it needs special syntax.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

The top manager has no manager, so manager_id is NULL for that row.

IS NULL and IS NOT NULL

query.sql

SQL

1
2

SELECT name, manager_id FROM employees WHERE manager_id IS NULL;
SELECT name, manager_id FROM employees WHERE manager_id IS NOT NULL ORDER BY id;

Output

OUTPUT

+----------+------------+
| name     | manager_id |
+----------+------------+
| Asha Rao |       NULL |
+----------+------------+
1 row in set

+--------------+------------+
| name         | manager_id |
+--------------+------------+
| Ben Carter   |          1 |
| Chitra Nair  |          2 |
| Dev Malhotra |          2 |
| Esha Gupta   |          1 |
| Farid Khan   |          5 |
| Gita Menon   |          5 |
| Hari Iyer    |          1 |
+--------------+------------+
7 rows in set

= NULL never matches

query.sql

SQL

SELECT name FROM employees WHERE manager_id = NULL;

Output

OUTPUT

Empty set

manager_id = NULL is never true. It is NULL (unknown), and WHERE drops rows that are not true.

Comparisons skip NULL rows

A comparison such as manager_id <> 2 is unknown for the row with NULL, so that row disappears from the result, even though you might say “the top manager is not employee 2”:

query.sql

SQL

SELECT name, manager_id FROM employees WHERE manager_id <> 2 ORDER BY id;

Output

OUTPUT

+------------+------------+
| name       | manager_id |
+------------+------------+
| Ben Carter |          1 |
| Esha Gupta |          1 |
| Farid Khan |          5 |
| Gita Menon |          5 |
| Hari Iyer  |          1 |
+------------+------------+
5 rows in set

Asha (the top manager, NULL) is missing. To include her:

query.sql

SQL

SELECT name, manager_id FROM employees WHERE manager_id <> 2 OR manager_id IS NULL ORDER BY id;

Output

OUTPUT

+------------+------------+
| name       | manager_id |
+------------+------------+
| Asha Rao   |       NULL |
| Ben Carter |          1 |
| Esha Gupta |          1 |
| Farid Khan |          5 |
| Gita Menon |          5 |
| Hari Iyer  |          1 |
+------------+------------+
6 rows in set

The NULL-safe equal: <=>

MySQL has <=>, which treats two NULLs as equal and never returns NULL:

query.sql

SQL

1
2

SELECT name FROM employees WHERE manager_id <=> NULL;
SELECT 1 <=> 1 AS one_one, NULL <=> NULL AS null_null, 1 <=> NULL AS one_null;

Output

OUTPUT

+----------+
| name     |
+----------+
| Asha Rao |
+----------+
1 row in set

+---------+-----------+----------+
| one_one | null_null | one_null |
+---------+-----------+----------+
|       1 |         1 |        0 |
+---------+-----------+----------+
1 row in set

It is useful when comparing two columns that may both be NULL: WHERE a <=> b. It is MySQL-specific. The standard version is a IS NOT DISTINCT FROM b, which MySQL does not support.

Replacing NULL

query.sql

SQL

SELECT name, IFNULL(manager_id, 0) AS manager_or_zero, COALESCE(manager_id, -1) AS manager_or_minus_one FROM employees ORDER BY id LIMIT 3;

Output

OUTPUT

+-------------+-----------------+----------------------+
| name        | manager_or_zero | manager_or_minus_one |
+-------------+-----------------+----------------------+
| Asha Rao    |               0 |                   -1 |
| Ben Carter  |               1 |                    1 |
| Chitra Nair |               2 |                    2 |
+-------------+-----------------+----------------------+
3 rows in set

IFNULL(x, y) takes two values. COALESCE(a, b, c, ...) takes many and returns the first one that is not NULL. COALESCE is standard SQL.

NULL in counting

query.sql

SQL

SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS rows_with_manager FROM employees;

Output

OUTPUT

+----------+-------------------+
| all_rows | rows_with_manager |
+----------+-------------------+
|        8 |                 7 |
+----------+-------------------+
1 row in set

COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not NULL.

NULL and NOT IN

This was covered in IN and NOT IN. Remember: if a list or subquery can contain NULL, NOT IN returns nothing. Use NOT EXISTS.

NULL and indexes

IS NULL can use an index, because MySQL indexes NULL values (unlike Oracle). Searching WHERE manager_id IS NULL on an indexed column is a fast lookup.

Design advice

A column should be NULL only when “unknown” or “not applicable” is a genuine state. For a required value, use NOT NULL.
Do not use special values such as 0, '' or '1900-01-01' to mean “missing”. Use NULL, and let the database treat it correctly.
Be consistent in a table: do not use both NULL and '' for “no value”.

warning

Counting with a NULL

SELECT AVG(bonus) ignores NULL bonuses. If “no bonus” should count as 0, use AVG(COALESCE(bonus, 0)). The two give different answers.