How NULL behaves in MySQL comparisons, functions, aggregates, sorting, constraints and indexes, with the queries that silently drop rows and how to fix them.
To find rows where a column has no value in MySQL, use IS NULL. The = operator never matches NULL:
SELECT id, name FROM students WHERE email ISNULL; -- correctSELECT id, name FROM students WHERE email =NULL; -- always returns zero rows
NULL means "no value" or "unknown". It is not zero, not an empty string and not FALSE. Almost every surprise with NULL comes from one rule: comparing anything with NULL gives NULL, and a WHERE clause only keeps rows where the condition is true.
The example table
Every query below runs against this table:
CREATE TABLE students (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NULL,
score INTNULL
);
INSERT INTO students (name, email, score) VALUES
('Asha', 'asha@example.com', 82),
('Bruno', NULL, 67),
('Chen', 'chen@example.com', ),
(, , ),
(, , );
The error points at a query that returned arbitrary values. Fix it with aggregates, primary-key grouping, ANY_VALUE() or window functions, and keep the SQL mode on.
=, <>, <, >, LIKE, IN and BETWEEN all return NULL when an operand is NULL.
IS NULL and IS NOT NULL always return 1 or 0.
<=> is MySQL's NULL-safe equality operator. It behaves like = but treats two NULLs as equal, which is what the SQL standard calls IS NOT DISTINCT FROM. It is useful when comparing two columns that can both be NULL, or when a prepared statement parameter might be NULL:
SELECT id, name FROM students WHERE email <=>NULL; -- same rows as IS NULL
+----+-------+
| id | name |
+----+-------+
| 2 | Bruno |
| 4 | Dana |
+----+-------+
Logic follows the same "unknown" rule: NULL AND 0 is 0, NULL OR 1 is 1, and every other combination with NULL, including NOT NULL, is NULL.
The filters that silently drop rows
A condition that evaluates to NULL is treated as not true, so the row is left out. This query looks like it should return everyone who did not score 67:
Chen and Dana are missing because NULL <> 67 is NULL. To include them, say so:
SELECT name FROM students WHERE score <>67OR score ISNULL;
NOT IN has the worst version of this problem. If the list or subquery contains a single NULL, the condition can never be true and the query returns nothing:
SELECT name FROM students WHERE score NOTIN (67, NULL); -- Empty set
When the list comes from a subquery on a nullable column, filter the NULLs out (WHERE col IS NOT NULL) or use NOT EXISTS, which does not have this behaviour.
NULL in expressions and functions
Arithmetic and most functions return NULL if any argument is NULL: score + 10, CONCAT(name, email), GREATEST(score, 50) and LENGTH(email) are all NULL for the affected rows. The functions that exist to deal with this are:
Function
What it returns
IFNULL(a, b)
b if a is NULL, otherwise a
COALESCE(a, b, c, ...)
the first argument that is not NULL (standard SQL, any number of arguments)
NULLIF(a, b)
NULL if a = b, otherwise a
ISNULL(a)
1 if a is NULL, otherwise 0
CONCAT_WS(sep, a, b, ...)
joins the arguments, skipping NULL ones
SELECT name,
COALESCE(NULLIF(email, ''), 'no email') AS contact,
IFNULL(score, 0) AS score_or_zero
FROM students
ORDERBY id;
+-------+------------------+---------------+
| name | contact | score_or_zero |
+-------+------------------+---------------+
| Asha | asha@example.com | 82 |
| Bruno | no email | 67 |
| Chen | chen@example.com | 0 |
| Dana | no email | 0 |
| Emeka | no email | 91 |
+-------+------------------+---------------+
NULLIF(email, '') is there because of Emeka's row. In MySQL an empty string is a real value, different from NULL (Oracle Database treats them as the same; MySQL does not). WHERE email IS NULL does not find Emeka and WHERE email = '' does not find Bruno. Pick one representation for "missing" per column and stick to it.
Aggregates, GROUP BY and DISTINCT
Aggregate functions skip NULL values. COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL.
SELECTCOUNT(*) AS total_rows,
COUNT(score) AS with_score,
SUM(score) AS sum_score,
AVG(score) AS avg_of_scored,
AVG(IFNULL(score, 0)) AS avg_of_all
FROM students;
The two averages answer different questions, so decide which one you mean. Also note that SUM, AVG, MIN and MAX return NULL, not 0, when there are no non-NULL values to aggregate. Wrap the aggregate, as in COALESCE(SUM(score), 0), if the application expects a number.
GROUP BY and DISTINCT are the exception to "NULL never equals NULL": they put all NULLs in one group.
Sorting
MySQL sorts NULL first in ascending order and last in descending order. It has no NULLS FIRST / NULLS LAST syntax. To push NULLs to the end of an ascending sort, sort on the IS NULL test first:
SELECT name, score FROM students ORDERBY score ISNULL, score, id;
NOT NULL. In strict SQL mode, which is the default, inserting NULL into a NOT NULL column fails:
INSERT INTO students (name) VALUES (NULL);
ERROR 1048 (23000): Column 'name' cannot be null
The one deliberate exception is an AUTO_INCREMENT column: inserting NULL there asks MySQL for the next number.
Adding NOT NULL later. The change is rejected while NULLs exist. Backfill first:
ALTER TABLE students MODIFY score INTNOT NULL;
-- ERROR 1138 (22004): Invalid use of NULL valueUPDATE students SET score =0WHERE score ISNULL;
ALTER TABLE students MODIFY score INTNOT NULL;
UNIQUE. A unique index allows any number of NULLs, because no two NULLs are equal. ALTER TABLE students ADD UNIQUE KEY uq_students_email (email); succeeds on the sample data even though two rows have a NULL email. If you need "at most one row without a value", NULL will not enforce it.
PRIMARY KEY. Primary key columns are always NOT NULL.
CHECK. A check constraint is violated only when its condition is false. A NULL result passes, so CHECK (score >= 0) accepts a NULL score. Add NOT NULL if the value is required.
Indexes. InnoDB stores NULLs in B-tree indexes, and WHERE email IS NULL can use an index on email like any equality lookup. You do not need a special index for it. Confirm with EXPLAIN.
Common mistakes
WHERE col = NULL or col != NULL. Runs without error and matches nothing. Use IS NULL / IS NOT NULL.
NOT IN (subquery) on a nullable column. Returns zero rows as soon as the subquery yields one NULL.
Expecting <> to include NULL rows. Add OR col IS NULL, or write NOT (col <=> 'value').
Building strings with CONCAT. One NULL part makes the whole result NULL. Use CONCAT_WS or COALESCE each part.
Mixing '' and NULL for "missing". Queries then need to check both. Normalise on input with NULLIF(value, '').
Outer joins. Columns from the unmatched side of a LEFT JOIN come back as NULL. A WHERE condition on those columns removes the unmatched rows and turns the join into an inner join; put the condition in the ON clause instead. The same behaviour is useful on purpose: LEFT JOIN ... WHERE right_table.id IS NULL finds rows with no match.
Passing NULL to application code. Drivers map SQL NULL to null / None / nil. Handle it there, or use COALESCE in the query.
When to allow NULL
Declare a column NOT NULL whenever a value is always required, and give it a DEFAULT if there is a sensible one. Allow NULL when "unknown" or "not applicable" is a real state that differs from zero or an empty string, such as an end date that has not happened yet. Fewer nullable columns means fewer of the edge cases on this page.
Page 1 takes 5 ms and page 5,000 takes 4 seconds. OFFSET reads and throws away every skipped row. Switch to keyset (cursor) pagination for constant-time pages.
MySQL's utf8 isn't real UTF-8. Here's why emoji break it, how to convert tables and connections to utf8mb4, and how to get past the 767-byte index limit.
