NULL Values

Real data has gaps. Two of the store's customers never gave an email address, and the chief executive has no manager. SQL marks a missing value with a special marker called NULL.

NULL causes more silent bugs than almost anything else in SQL, because it does not behave like a normal value. In this lesson you will learn what NULL means, why = NULL never works, how to find NULLs with IS NULL, and how to replace them with COALESCE and IFNULL.

What NULL means

NULL means "no value here" or "we do not know". It is not the same as zero, and it is not the same as an empty string ''.

• A stock of 0 is a real value: we know there are no webcams left.
• An email of '' (empty string) is also a value: someone saved blank text.
• An email of NULL means the store simply has no email address for that customer.

When you look at the customers table, customers 4 (Ananya) and 9 (Arjun) show NULL in the email column. In the employees table, employee 1 (Rajesh) has a NULL manager_id.

Why = NULL finds nothing

Try the obvious way to find the customers with no email:

SELECT first_name FROM customers WHERE email = NULL;

first_name
----------
(0 rows)

No rows, and no error either. The reason is that any comparison with NULL gives neither true nor false, but a third result: unknown, which SQL also shows as NULL. Think about it in plain words. Is an unknown email equal to an unknown value? You cannot say. And WHERE only keeps rows for which the condition is true.

You can see this directly:

SELECT NULL = NULL, 5 > NULL, NULL + 100;

NULL = NULL | 5 > NULL | NULL + 100
------------+----------+-----------
NULL        | NULL     | NULL
(1 row)

Even NULL = NULL is unknown, and arithmetic with NULL gives NULL too. Adding ₹100 to an unknown amount gives an unknown amount.

IS NULL and IS NOT NULL

SQL has a special test for missing values. IS NULL is true when a value is NULL, and IS NOT NULL is true when it is not.

SELECT customer_id, first_name, city
FROM customers
WHERE email IS NULL;

customer_id | first_name | city
------------+------------+----------
4           | Ananya     | Chennai
9           | Arjun      | Hyderabad
(2 rows)

Now you find Ananya and Arjun. Turn it around with IS NOT NULL to get the ten customers who can receive an email newsletter. In the employees table, WHERE manager_id IS NULL finds the person at the top of the company.

NULL hides rows from "not equal" tests

Unknown results catch people out with <> too. Suppose you want every employee who does not report to Neha (employee 2):

SELECT first_name, manager_id
FROM employees
WHERE manager_id <> 2;

first_name | manager_id
-----------+-----------
Neha       | 1
Simran     | 1
Rahul      | 6
Nisha      | 6
Deepak     | 1
Tanvi      | 9
(6 rows)

Rajesh is missing. He does not report to Neha, but his manager_id is NULL, and NULL <> 2 is unknown, so the row is dropped. If you want him included, say so explicitly:

SELECT first_name, manager_id
FROM employees
WHERE manager_id <> 2 OR manager_id IS NULL;

first_name | manager_id
-----------+-----------
Rajesh     | NULL
Neha       | 1
Simran     | 1
Rahul      | 6
Nisha      | 6
Deepak     | 1
Tanvi      | 9
(7 rows)

Whenever a column can contain NULL, ask yourself what should happen to those rows, and write the condition to match.

Replacing NULL with COALESCE and IFNULL

When you show data to people, NULL looks ugly or confusing. COALESCE() takes a list of values and returns the first one that is not NULL. Use it to give a fallback:

SELECT first_name, COALESCE(email, 'not given') AS contact
FROM customers
WHERE customer_id <= 5;

first_name | contact
-----------+-------------------------
Aarav      | aarav.sharma@example.com
Priya      | priya.verma@example.com
Rohan      | rohan.mehta@example.com
Ananya     | not given
Kabir      | kabir.singh@example.com
(5 rows)

Customers with an email show it; Ananya shows "not given". The AS contact part names the column, which you will learn about in Aliases. Only the result changes: the table still holds NULL.

MySQL also has IFNULL(value, fallback), which does the same job for exactly two values. COALESCE is standard SQL, works in every database and accepts more than two values, for example COALESCE(mobile, email, 'no contact'), so prefer it.

NULL elsewhere in SQL

You will meet NULL again in later lessons. Here is what to expect:

To stop NULLs getting into a column at all, define it as NOT NULL when you create the table, as explained in Constraints & Keys.

Common mistakes

• Writing = NULL or <> NULL. They always give unknown, so the query returns no rows. Use IS NULL and IS NOT NULL.
• Treating NULL as zero or blank. NULL + 100 is NULL, not 100. Wrap the column in COALESCE(column, 0) if you want missing numbers treated as zero.
• Typing the text 'NULL'. WHERE email = 'NULL' looks for the four letters N, U, L, L. NULL is a keyword and never goes in quotes.
• Forgetting NULL rows in "not equal" filters. <> silently drops rows where the column is NULL; add OR column IS NULL when you need them.

Key points

• NULL means a missing or unknown value; it is not 0 and not an empty string.
• Any comparison or arithmetic with NULL gives NULL (unknown), and WHERE drops unknown rows.
• Use IS NULL and IS NOT NULL to test for missing values.
• COALESCE(a, b, ...) returns the first non-NULL value; IFNULL(a, b) is MySQL's two-value version.
• MySQL's <=> operator compares values with NULL treated as an ordinary value.

SELECT first_name, last_name,
       COALESCE(email, 'call the customer') AS contact
FROM customers;

SELECT COUNT(*) FROM customers WHERE email IS NOT NULL;