Chapter 09 of 35

NULL Values

In the last two chapters, rows kept going "missing". city <> 'Mumbai' skipped Ishita Gupta, email = NULL found nothing, and NOT IN quietly dropped a row. Every one of these surprises had the same cause: NULL.

NULL is probably the most misunderstood concept in SQL, and also one of the most asked about in interviews. In this chapter we will understand what NULL really means, why comparisons with it behave so strangely (three-valued logic), how to test for it correctly, and how to replace it with sensible values using IFNULL, COALESCE and NULLIF.

All examples use the shared company_db sample database, with real MySQL 8.4 output.


What is NULL?

NULL is a marker that means a value is missing or unknown. It is not a value itself: it is the absence of a value.

Our sample data has several NULLs, each with a real-world meaning:

Row

Column that is NULL

Meaning

Meera Nair

email

We do not have her email (yet)

Ishita Gupta

dept_id, city

Not yet assigned to a department; city not recorded

Aarav Sharma

manager_id

He is the top boss; there is no manager

Order 1008

customer_id

A guest checkout with no customer account

Notice that NULL can mean "unknown" (we do not know Meera's email) or "not applicable" (Aarav has no manager). SQL uses the same single marker for both.

NULL is Not Zero, and Not an Empty String

This is the first thing to get right:

  • 0 is a known number. A salary of 0 means "paid nothing".

  • '' (empty string) is a known string with zero characters. An email of '' means "the user typed nothing".

  • NULL means "we don't know". A salary of NULL means "we don't know the salary".

SELECT ISNULL(NULL), ISNULL(''), ISNULL(0), '' IS NULL, 0 IS NULL;
+--------------+------------+-----------+------------+-----------+
| ISNULL(NULL) | ISNULL('') | ISNULL(0) | '' IS NULL | 0 IS NULL |
+--------------+------------+-----------+------------+-----------+
|            1 |          0 |         0 |          0 |         0 |
+--------------+------------+-----------+------------+-----------+
1 row in set (0.00 sec)

Only the real NULL is NULL. So searching for an empty string does not find Meera:

SELECT name, email FROM employees WHERE email = '';
Empty set (0.00 sec)

(Oracle is a famous exception: it treats an empty string as NULL. MySQL, PostgreSQL and SQL Server do not.)


Testing for NULL: IS NULL and IS NOT NULL

You cannot find NULLs with =. You must use the special operators IS NULL and IS NOT NULL.

SELECT name, email FROM employees WHERE email IS NULL;
+------------+-------+
| name       | email |
+------------+-------+
| Meera Nair | NULL  |
+------------+-------+
1 row in set (0.00 sec)
SELECT order_id, customer_id, amount FROM orders WHERE customer_id IS NULL;
+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
|     1008 |        NULL | 800.00 |
+----------+-------------+--------+
1 row in set (0.00 sec)

The employee at the top of the hierarchy (no manager):

SELECT name, manager_id FROM employees WHERE manager_id IS NULL;
+--------------+------------+
| name         | manager_id |
+--------------+------------+
| Aarav Sharma |       NULL |
+--------------+------------+
1 row in set (0.00 sec)

And the opposite, how many employees do have an email:

SELECT COUNT(*) FROM employees WHERE email IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

(COUNT(*) counts rows; aggregate functions get their own chapter later.)


Why = NULL Does Not Work

Think about what NULL means: "unknown". If I ask "is an unknown value equal to 1?", the honest answer is not yes or no. It is "I don't know". SQL represents that answer as a third truth value, UNKNOWN, which MySQL displays as NULL.

SELECT NULL = NULL, NULL <> NULL, NULL = 1, NULL > 0,
       NULL IS NULL, NULL <=> NULL, 1 <=> NULL;
+-------------+--------------+----------+----------+--------------+---------------+------------+
| NULL = NULL | NULL <> NULL | NULL = 1 | NULL > 0 | NULL IS NULL | NULL <=> NULL | 1 <=> NULL |
+-------------+--------------+----------+----------+--------------+---------------+------------+
|        NULL |         NULL |     NULL |     NULL |            1 |             1 |          0 |
+-------------+--------------+----------+----------+--------------+---------------+------------+
1 row in set (0.00 sec)

Even NULL = NULL is UNKNOWN. Two unknown values are not known to be equal: if two people's ages are both unknown, you cannot say they are the same age.

Recall the rule from the WHERE Clause chapter: WHERE keeps only rows where the condition is TRUE. UNKNOWN is not TRUE, so:

WHERE email = NULL
  Row 'Aarav':  'aarav@company.com' = NULL  → UNKNOWN → dropped
  Row 'Meera':  NULL = NULL                 → UNKNOWN → dropped
  ... every row → UNKNOWN → Empty set

IS NULL is different: it is a test about whether a value is missing, and it always answers TRUE or FALSE, never UNKNOWN.


Three-Valued Logic

Because comparisons can be TRUE, FALSE or UNKNOWN, AND, OR and NOT need rules for UNKNOWN too. Let's ask MySQL directly:

SELECT TRUE AND NULL, FALSE AND NULL, TRUE OR NULL, FALSE OR NULL,
       NOT NULL, NULL AND NULL, NULL OR NULL;
+---------------+----------------+--------------+---------------+----------+---------------+--------------+
| TRUE AND NULL | FALSE AND NULL | TRUE OR NULL | FALSE OR NULL | NOT NULL | NULL AND NULL | NULL OR NULL |
+---------------+----------------+--------------+---------------+----------+---------------+--------------+
|          NULL |              0 |            1 |          NULL |     NULL |          NULL |         NULL |
+---------------+----------------+--------------+---------------+----------+---------------+--------------+
1 row in set (0.00 sec)

The full truth tables (T = TRUE, F = FALSE, U = UNKNOWN):

AND

T

F

U

T

T

F

U

F

F

F

F

U

U

F

U

OR

T

F

U

T

T

T

T

F

T

F

U

U

T

U

U

A

NOT A

T

F

F

T

U

U

You do not need to memorise these. Just ask: "could the unknown value change the answer?"

  • FALSE AND ?: whatever ? is, the result is FALSE. So it is FALSE.

  • TRUE OR ?: whatever ? is, the result is TRUE. So it is TRUE.

  • TRUE AND ?: depends on ?. So it is UNKNOWN.

  • NOT ?: the opposite of unknown is still unknown. So UNKNOWN.

Why NOT Doesn't Bring Back NULL Rows

That last rule explains a puzzle from the previous chapter:

SELECT name, city FROM employees WHERE NOT (city = 'Mumbai');
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer   | Chennai   |
| Rohan Mehta  | Bengaluru |
| Ananya Das   | Kolkata   |
| Karan Patel  | Delhi     |
| Meera Nair   | Kochi     |
| Arjun Reddy  | Hyderabad |
+--------------+-----------+
7 rows in set (0.00 sec)

For Ishita: city = 'Mumbai' → UNKNOWN, NOT UNKNOWN → UNKNOWN → dropped. A condition and its NOT together do not cover every row: rows where the condition is UNKNOWN fall through both. If you want them, ask for them:

SELECT name, city FROM employees WHERE city <> 'Mumbai' OR city IS NULL;
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer   | Chennai   |
| Rohan Mehta  | Bengaluru |
| Ananya Das   | Kolkata   |
| Karan Patel  | Delhi     |
| Meera Nair   | Kochi     |
| Arjun Reddy  | Hyderabad |
| Ishita Gupta | NULL      |
+--------------+-----------+
8 rows in set (0.00 sec)

Now all 8 non-Mumbai employees appear. For Ishita: UNKNOWN OR TRUE → TRUE.


The NOT IN Trap

This is the most famous NULL bug, and interviewers love it. Recall that x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. Watch what happens when the list contains a NULL:

SELECT name, dept_id FROM employees WHERE dept_id NOT IN (10, 20, NULL);
Empty set (0.00 sec)

Not a single row, even though Karan (30), Meera (30) and Arjun (40) are clearly not in departments 10 or 20. Let's trace Karan's row:

30 NOT IN (10, 20, NULL)
= 30 <> 10  AND  30 <> 20  AND  30 <> NULL
=   TRUE    AND    TRUE    AND   UNKNOWN
=   UNKNOWN                      → row dropped

And a row whose value is in the list:

10 NOT IN (10, 20, NULL)
= 10 <> 10  AND ...
=   FALSE   AND ...   → FALSE    → row dropped

So with a NULL in the list, NOT IN can never be TRUE, for any row. MySQL confirms both traces:

SELECT 30 NOT IN (10, 20, NULL) AS r1, 10 NOT IN (10, 20, NULL) AS r2, 30 IN (10, NULL) AS r3;
+------+------+------+
| r1   | r2   | r3   |
+------+------+------+
| NULL |    0 | NULL |
+------+------+------+
1 row in set (0.00 sec)

IN is less dangerous: 30 IN (10, NULL) is UNKNOWN, but matching values are still TRUE, so WHERE dept_id IN (10, NULL) correctly returns the three department-10 employees.

Nobody types NULL into a list on purpose. The trap appears when the list comes from a subquery, e.g. "departments whose dept_id is NOT IN the list of employees' dept_ids". Because Ishita's dept_id is NULL, that list contains a NULL, and the query returns nothing, even though the Research department clearly has no employees. The fixes (filtering out NULLs inside the subquery, or using NOT EXISTS) are covered in the Subqueries and Correlated Subqueries & EXISTS chapters.

Rule: if a NOT IN list can possibly contain NULL, the query will silently return no rows.


NULL in Expressions: NULL Spreads

Almost any calculation involving NULL produces NULL. If one input is unknown, the result is unknown:

SELECT NULL + 5, NULL * 0, CONCAT('Hi ', NULL), UPPER(NULL), LENGTH(NULL);
+----------+----------+---------------------+-------------+--------------+
| NULL + 5 | NULL * 0 | CONCAT('Hi ', NULL) | UPPER(NULL) | LENGTH(NULL) |
+----------+----------+---------------------+-------------+--------------+
|     NULL |     NULL | NULL                | NULL        |         NULL |
+----------+----------+---------------------+-------------+--------------+
1 row in set (0.00 sec)

Even NULL * 0 is NULL, although anything times zero "should" be zero. SQL does not special-case it. This is why, in the SELECT Statement chapter, CONCAT(name, ' (', city, ')') returned NULL for Ishita. A single NULL input wipes out the whole result. The next section shows how to fix it.


Replacing NULL: IFNULL and COALESCE

IFNULL(expr, replacement)

MySQL's IFNULL returns the first argument if it is not NULL, otherwise the second:

SELECT name, IFNULL(email, 'not provided') AS email
FROM employees
WHERE emp_id IN (7, 8, 9);
+-------------+-------------------+
| name        | email             |
+-------------+-------------------+
| Karan Patel | karan@company.com |
| Meera Nair  | not provided      |
| Arjun Reddy | arjun@company.com |
+-------------+-------------------+
3 rows in set (0.00 sec)

Like any function in SELECT, this changes only the output. Meera's stored email is still NULL.

COALESCE(expr1, expr2, ...)

COALESCE is the SQL-standard version and accepts any number of arguments. It returns the first one that is not NULL:

SELECT COALESCE(NULL, NULL, 'third', 'fourth') AS first_non_null,
       COALESCE(NULL, NULL) AS all_null;
+----------------+----------+
| first_non_null | all_null |
+----------------+----------+
| third          |     NULL |
+----------------+----------+
1 row in set (0.00 sec)

If every argument is NULL, the result is NULL. A typical use is a fallback chain such as COALESCE(mobile, landline, email, 'no contact').

Now we can fix the broken CONCAT from the SELECT Statement chapter:

SELECT CONCAT(name, ' (', COALESCE(city, 'Remote'), ')') AS name_with_city
FROM employees
WHERE emp_id >= 8;
+-------------------------+
| name_with_city          |
+-------------------------+
| Meera Nair (Kochi)      |
| Arjun Reddy (Hyderabad) |
| Ishita Gupta (Remote)   |
+-------------------------+
3 rows in set (0.00 sec)

Labelling the guest order:

SELECT order_id, IFNULL(customer_id, 'Guest') AS customer
FROM orders
WHERE order_id IN (1007, 1008);
+----------+----------+
| order_id | customer |
+----------+----------+
|     1007 | 101      |
|     1008 | Guest    |
+----------+----------+
2 rows in set (0.00 sec)

Look carefully: 101 is now left-aligned. Because one possible result is the string 'Guest', the whole column becomes a string column, and the mysql client left-aligns strings. Mixing types in IFNULL/COALESCE changes the result type, which matters if your application expects a number.

IFNULL

COALESCE

MySQL-specific (SQL Server has ISNULL, Oracle has NVL)

SQL standard; works everywhere

Exactly 2 arguments

Any number of arguments

IFNULL(a, b)

COALESCE(a, b) gives the same result

Be careful with names: MySQL's ISNULL(x) takes one argument and returns 1 or 0 (we used it above), while SQL Server's ISNULL(x, y) is a replacement function like IFNULL.


NULLIF: Turning a Value into NULL

NULLIF(a, b) does the opposite job: it returns NULL if a = b, otherwise it returns a.

SELECT NULLIF(5, 5), NULLIF(5, 3), 100 / NULLIF(0, 0), 100 / 0;
+--------------+--------------+--------------------+---------+
| NULLIF(5, 5) | NULLIF(5, 3) | 100 / NULLIF(0, 0) | 100 / 0 |
+--------------+--------------+--------------------+---------+
|         NULL |            5 |               NULL |    NULL |
+--------------+--------------+--------------------+---------+
1 row in set, 1 warning (0.00 sec)

Its classic use is preventing division by zero: total / NULLIF(count, 0) gives NULL instead of failing when count is 0. In a plain SELECT, MySQL's 100 / 0 also returns NULL, but with a warning ("Division by 0"), and inside an INSERT or UPDATE under the default strict SQL mode it is an error. Many other databases always raise an error. NULLIF makes the intent explicit and works everywhere.

Another use: treating empty strings as missing, e.g. COALESCE(NULLIF(phone, ''), 'not given').


The Null-Safe Equal Operator <=>

MySQL has a special comparison operator, <=>, that treats NULLs as comparable. It always returns 1 or 0, never NULL:

Expression

=

<=>

1 vs 1

1

1

1 vs 2

0

0

1 vs NULL

NULL

0

NULL vs NULL

NULL

1

SELECT name, dept_id FROM employees WHERE dept_id <=> NULL;
+--------------+---------+
| name         | dept_id |
+--------------+---------+
| Ishita Gupta |    NULL |
+--------------+---------+
1 row in set (0.00 sec)

It is useful when comparing two columns that may both be NULL, for example when checking whether a value changed: old.city <=> new.city. The SQL-standard spelling is IS NOT DISTINCT FROM (supported by PostgreSQL; in MySQL use <=>). For a simple "is it NULL?" check, IS NULL is clearer.


NULL and Constraints

NOT NULL

A column declared NOT NULL refuses missing values. In company_db, employees.name is NOT NULL:

INSERT INTO employees (emp_id, name, salary, hire_date)
VALUES (11, NULL, 50000, '2024-05-01');
ERROR 1048 (23000): Column 'name' cannot be null

Primary key columns are always NOT NULL: a row must be identifiable. Deciding which columns may be NULL is a design decision: allow NULL only where "unknown" is a real, expected situation.

UNIQUE Allows Multiple NULLs

Here is a subtle one. Let's create a small throwaway table with a UNIQUE column (in a scratch copy of the database, not company_db):

CREATE TABLE null_demo (id INT PRIMARY KEY, code VARCHAR(10) UNIQUE);
INSERT INTO null_demo VALUES (1, NULL), (2, NULL), (3, 'A');
SELECT * FROM null_demo;
+----+------+
| id | code |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | A    |
+----+------+
3 rows in set (0.00 sec)

Two NULLs were accepted in a UNIQUE column, but a second 'A' is rejected:

INSERT INTO null_demo VALUES (4, 'A');
ERROR 1062 (23000): Duplicate entry 'A' for key 'null_demo.code'

Why? UNIQUE forbids two equal values, and NULL = NULL is not TRUE, so two NULLs are not considered duplicates. That is why the employees.email column could hold several NULLs if more employees lacked an email. (SQL Server is the odd one out here: by default its UNIQUE constraint allows only one NULL.) Constraints are covered fully in the SQL Constraints chapter.


A Preview: NULL in Sorting and Aggregates

NULL affects two more areas that have their own chapters:

  • Sorting: MySQL treats NULL as smaller than any value, so NULLs come first in ascending order. We will see this and how to change it in the next chapter, ORDER BY & LIMIT.

  • Aggregates: functions like COUNT(column), SUM and AVG ignore NULLs, while COUNT(*) counts every row. In our table, COUNT(*) is 10 but COUNT(email) is 9. Details in the Aggregate Functions chapter.


Common Mistakes

Mistake

What happens

Correct

WHERE col = NULL

Always empty

WHERE col IS NULL

WHERE col <> NULL / != NULL

Always empty

WHERE col IS NOT NULL

WHERE col <> 'x' expecting NULL rows

NULL rows missing

WHERE col <> 'x' OR col IS NULL

NOT IN with a list or subquery that may contain NULL

No rows at all

Exclude NULLs, or use NOT EXISTS

Treating NULL as 0 or ''

Wrong searches, wrong totals

Use IS NULL; convert with COALESCE when needed

salary + bonus where bonus may be NULL

Whole result NULL

salary + COALESCE(bonus, 0)

Assuming UNIQUE blocks duplicate NULLs

Many NULLs allowed

Add NOT NULL if every row needs a value


Interview Questions

What is NULL in SQL? Is it the same as 0 or an empty string?

NULL is a marker for a missing or unknown value. It is not 0 and not '' (in MySQL); those are known values. '' IS NULL and 0 IS NULL are both false.

Why does WHERE col = NULL return no rows?

Any comparison with NULL yields UNKNOWN, and WHERE keeps only TRUE rows. Use IS NULL / IS NOT NULL.

What is three-valued logic?

SQL conditions can be TRUE, FALSE or UNKNOWN. UNKNOWN arises from comparisons with NULL. Rules: FALSE AND U = FALSE, TRUE OR U = TRUE, NOT U = U, and other combinations with U give U.

What does NULL = NULL return?

NULL (UNKNOWN), not TRUE. MySQL's NULL <=> NULL returns 1.

Why can NOT IN return no rows?

x NOT IN (a, NULL) expands to x <> a AND x <> NULL; the second part is UNKNOWN, so the whole condition is never TRUE. It happens when a subquery's result contains NULL. Use NOT EXISTS or filter out NULLs.

Difference between IFNULL and COALESCE?

IFNULL is MySQL-specific and takes exactly two arguments. COALESCE is standard SQL, takes any number, and returns the first non-NULL one.

What is NULLIF used for?

NULLIF(a, b) returns NULL when a = b, else a. Commonly x / NULLIF(y, 0) to avoid division-by-zero errors.

Can a UNIQUE column contain multiple NULLs?

In MySQL (and PostgreSQL, Oracle), yes, because NULLs are not equal to each other. A PRIMARY KEY column cannot contain NULL at all.


Summary

  • NULL means "missing or unknown". It is not 0 and not an empty string.

  • Test for it only with IS NULL / IS NOT NULL (or MySQL's <=>).

  • Comparisons with NULL give UNKNOWN; WHERE drops UNKNOWN rows, and NOT does not bring them back.

  • Three-valued logic: FALSE AND U = FALSE, TRUE OR U = TRUE, everything else with U is U.

  • A NULL in a NOT IN list makes the condition never TRUE.

  • Expressions with NULL usually become NULL; use IFNULL / COALESCE for defaults and NULLIF to create NULLs (e.g. avoid division by zero).

  • NOT NULL forbids NULLs; UNIQUE allows many NULLs.

Next, in ORDER BY & LIMIT, we will finally control the order of our results, see where NULLs go when sorting, and learn to fetch "top N" rows and pages of data.