NULL values

What NULL means

Sometimes a value is simply not known or does not apply. A customer might not have given a phone number; a new employee might not yet have a manager. SQL stores that as NULL.

NULL is not a value like 0 or an empty string ''. A phone number of '' says "we know it is empty", while NULL says "we do not know". That difference sounds small, but it changes how every comparison behaves.

In the sample data four customers have no phone number:

SELECT id, first_name, phone FROM customers WHERE phone IS NULL;

+----+------------+-------+
| id | first_name | phone |
+----+------------+-------+
|  2 | Ben        | NULL  |
|  5 | Elena      | NULL  |
|  9 | Isabel     | NULL  |
| 12 | Liam       | NULL  |
+----+------------+-------+

Notice that the server prints NULL in the output where the value is missing.

Test with IS NULL

The natural idea, WHERE phone = NULL, does not work. Comparing anything with NULL produces NULL, which WHERE treats as "not true":

SELECT NULL = NULL AS equal, NULL <> 1 AS different, NULL IS NULL AS is_null;

+-------+-----------+---------+
| equal | different | is_null |
+-------+-----------+---------+
|  NULL |      NULL |       1 |
+-------+-----------+---------+

SELECT id, first_name FROM customers WHERE phone = NULL;

That second query returns an empty set, even though four customers have a missing phone. Use IS NULL and IS NOT NULL:

SELECT id, first_name, phone FROM customers WHERE phone IS NOT NULL AND country = 'India';

+----+------------+-----------------+
| id | first_name | phone           |
+----+------------+-----------------+
|  1 | Asha       | +91-98100-00001 |
|  6 | Farid      | +91-98100-00006 |
| 11 | Kavya      | +91-98100-00011 |
+----+------------+-----------------+

MySQL also has a null-safe equals operator, <=>, which treats two NULLs as equal: NULL <=> NULL is 1. It is handy when comparing two columns that may both be empty.

Three-valued logic

Because a condition can be true, false or unknown, SQL uses three-valued logic. WHERE keeps only rows where the condition is true, so unknown rows are dropped just like false ones. This makes some results surprising:

SELECT COUNT(*) AS all_customers,
       SUM(country = 'India') AS in_india,
       SUM(phone LIKE '+91%') AS indian_phones
FROM customers;

+---------------+----------+---------------+
| all_customers | in_india | indian_phones |
+---------------+----------+---------------+
|            12 |        3 |             3 |
+---------------+----------+---------------+

Rows whose phone is NULL give an unknown result for the LIKE test, and SUM ignores unknown values. Always ask yourself what happens to the NULL rows when you write a condition on a column that allows them.

NULL in arithmetic and text

Any calculation with NULL is NULL:

SELECT 5 + NULL AS sum_with_null, CONCAT('Tel: ', NULL) AS text_with_null;

+---------------+----------------+
| sum_with_null | text_with_null |
+---------------+----------------+
|          NULL | NULL           |
+---------------+----------------+

CONCAT with a NULL part returns NULL. If you want a replacement, wrap the column in IFNULL (two arguments) or COALESCE (the first non-NULL of many):

SELECT first_name, IFNULL(phone, 'no phone') AS phone FROM customers WHERE id IN (1, 2);

+------------+-----------------+
| first_name | phone           |
+------------+-----------------+
| Asha       | +91-98100-00001 |
| Ben        | no phone        |
+------------+-----------------+

Counting and NULL

Aggregate functions mostly skip NULLs. COUNT(*) counts rows; COUNT(column) counts only non-NULL values in that column:

SELECT COUNT(*) AS customers, COUNT(phone) AS with_phone FROM customers;

+-----------+------------+
| customers | with_phone |
+-----------+------------+
|        12 |          8 |
+-----------+------------+

NULL in sorting

When sorting, MySQL treats NULL as the smallest value. It comes first with ASC and last with DESC:

SELECT first_name, phone FROM customers ORDER BY phone LIMIT 5;

+------------+-----------------+
| first_name | phone           |
+------------+-----------------+
| Liam       | NULL            |
| Ben        | NULL            |
| Isabel     | NULL            |
| Elena      | NULL            |
| Jack       | +1-212-555-0010 |
+------------+-----------------+

To put missing values last in an ascending sort, sort on phone IS NULL first: ORDER BY phone IS NULL, phone.

The NOT IN trap

NOT IN is dangerous when the list contains NULL:

SELECT 5 IN (1, 2, NULL) AS a, 5 NOT IN (1, 2, NULL) AS b, 1 IN (1, NULL) AS c;

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL |    1 |
+------+------+------+

5 IN (1, 2, NULL) is not false but unknown: 5 might equal the unknown value. The negation is unknown as well, so a NOT IN that meets even one NULL returns no rows at all. This often shows up with subqueries such as WHERE id NOT IN (SELECT manager_id FROM employees), where manager_id has a NULL for the top boss. Use NOT EXISTS (a later lesson), or add WHERE manager_id IS NOT NULL inside the subquery.

Common mistakes

• Writing = NULL or <> NULL. Both are never true.
• Treating NULL like 0 or ''.
• Forgetting that <> drops NULL rows. WHERE country <> 'India' skips customers whose country is NULL (not an issue here, because country is declared NOT NULL).
• Using COUNT(column) when you meant COUNT(*), which silently undercounts.
• Using NOT IN against a nullable column.

Exercise

Write queries that return: (1) employees who have no manager, (2) employees with no department, and (3) employees with a department but a manager other than 3, being careful that NULL managers are handled as you intend.

SELECT name FROM employees WHERE manager_id IS NULL;
SELECT name FROM employees WHERE department_id IS NULL;
SELECT name, manager_id FROM employees WHERE department_id IS NOT NULL AND (manager_id <> 3 OR manager_id IS NULL);

+------------+
| name       |
+------------+
| Meera Iyer |
+------------+
+-------------+
| name        |
+-------------+
| Victor Lang |
+-------------+
+-------------+------------+
| name        | manager_id |
+-------------+------------+
| Meera Iyer  |       NULL |
| Tom Hughes  |          1 |
| Nina Petrov |          1 |
| Quinn Baker |          2 |
| Rina Das    |          2 |
| Tara Singh  |          1 |
| Uma Reddy   |          4 |
+-------------+------------+