MySQL NULL Explained: What It Means and Why It Surprises You

NULL is the most misunderstood value in SQL. It means “no value” or “unknown”. It is not zero. It is not an empty string. It is the absence of a value.

A phone number column is NULL when you do not know the number. It is '' (empty string) only if you know there is a number and it is blank, which is rarely what you mean.

NULL is not equal to anything, not even NULL

query.sql

SQL

SELECT NULL = NULL AS equal, NULL <> NULL AS not_equal, NULL = 0 AS zero_check, NULL = '' AS empty_check;

Output

OUTPUT

+-------+-----------+------------+-------------+
| equal | not_equal | zero_check | empty_check |
+-------+-----------+------------+-------------+
|  NULL |      NULL |       NULL |        NULL |
+-------+-----------+------------+-------------+
1 row in set

Every comparison with NULL gives NULL (unknown), not true or false. Think: “Is an unknown number equal to another unknown number?” The honest answer is “I do not know”.

The right test: IS NULL

query.sql

SQL

SELECT NULL IS NULL AS is_null, 5 IS NULL AS five_is_null, 5 IS NOT NULL AS five_is_not_null;

Output

OUTPUT

+---------+--------------+------------------+
| is_null | five_is_null | five_is_not_null |
+---------+--------------+------------------+
|       1 |            0 |                1 |
+---------+--------------+------------------+
1 row in set

Use IS NULL and IS NOT NULL. Never = NULL.

NULL spreads through calculations

If any part of a calculation is NULL, the whole answer is NULL:

query.sql

SQL

SELECT 10 + NULL AS sum_result, CONCAT('Hi ', NULL) AS text_result;

Output

OUTPUT

+------------+-------------+
| sum_result | text_result |
+------------+-------------+
|       NULL | NULL        |
+------------+-------------+
1 row in set

NULL and a WHERE clause

WHERE keeps only rows where the condition is true. A NULL result is not true, so those rows disappear without an error:

query.sql

SQL

CREATE TABLE people (id INT, name VARCHAR(20), phone VARCHAR(20));
INSERT INTO people VALUES (1, 'Asha', '98450'), (2, 'Ben', NULL), (3, 'Chitra', '');

SELECT name FROM people WHERE phone <> '98450';

Output

OUTPUT

+--------+
| name   |
+--------+
| Chitra |
+--------+
1 row in set

Ben is missing, because NULL <> '98450' is unknown, not true. Chitra, with an empty string, is included. To include Ben:

query.sql

SQL

SELECT name FROM people WHERE phone <> '98450' OR phone IS NULL;

Output

OUTPUT

+--------+
| name   |
+--------+
| Ben    |
| Chitra |
+--------+
2 rows in set

Handling NULL in a result

Function	What it does
`IFNULL(x, y)`	Gives `y` when `x` is NULL (MySQL)
`COALESCE(a, b, c)`	The first value that is not NULL (standard SQL)
`NULLIF(a, b)`	NULL if `a` equals `b`, otherwise `a`

query.sql

SQL

SELECT name, COALESCE(phone, 'not provided') AS phone FROM people;

Output

OUTPUT

+--------+--------------+
| name   | phone        |
+--------+--------------+
| Asha   | 98450        |
| Ben    | not provided |
| Chitra |              |
+--------+--------------+
3 rows in set

Summary

NULL means unknown.
Test it with IS NULL, not =.
Calculations with NULL give NULL.
Decide per column whether NULL is allowed (NOT NULL forbids it). See NOT NULL and DEFAULT.
The next lesson-chapters show how NULL behaves in aggregates, sorting and joins.

Exercise: find the unknown phones

Using the people table above, write a query that lists the names of people whose phone is not recorded (NULL), not those with an empty string.

Show a possible solution

query.sql

SQL

SELECT name FROM people WHERE phone IS NULL;

Output

OUTPUT

+------+
| name |
+------+
| Ben  |
+------+
1 row in set