NULL semantics and the null-safe operator
Three-valued logic in MySQL: the <=> operator, why NOT IN goes empty, how aggregates skip NULL, where NULLs sort, and why a UNIQUE key allows many of them.
After this lesson you can
- Evaluate comparisons and boolean expressions that involve NULL
- Use <=> to compare nullable columns correctly
- Explain why NOT IN with a nullable subquery returns nothing
- Control where NULLs sort without changing the data
NULL means "unknown". Any comparison with an unknown value is itself
unknown, so SQL has three truth values: true, false and NULL. MySQL
represents booleans as 1 and 0, which makes the third value visible as a
plain NULL in a result.
SELECT NULL = NULL AS eq, NULL <=> NULL AS null_safe_eq, 1 <=> NULL AS one_null_safe, 1 IN (2, NULL) AS in_list, 1 NOT IN (2, NULL) AS not_in_list, NULL AND 0 AS and_false, NULL OR 1 AS or_true, CONCAT('a', NULL) AS concat, CONCAT_WS('-', 'a', NULL, 'b') AS concat_ws;Read the row left to right:
NULL = NULLisNULL, not true. UseIS NULLto test for it.<=>is MySQL's null-safe equality:NULL <=> NULLis1and1 <=> NULLis0. It never returnsNULL. The standard spelling isIS NOT DISTINCT FROM, which MySQL does not support.1 IN (2, NULL)isNULL: 1 is not 2, and nobody knows whether it equals the unknown.NOT INinherits the sameNULL.NULL AND 0is0andNULL OR 1is1, because the known side settles the answer.CONCATreturnsNULLif any argument isNULL;CONCAT_WSskips them.
CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR(20), manager_id INT, bonus INT);INSERT INTO emp VALUES (1, 'Ann', NULL, 100), (2, 'Bob', 1, NULL), (3, 'Cy', 1, 50), (4, 'Dee', 2, NULL);SELECT (SELECT COUNT(*) FROM emp WHERE id NOT IN (SELECT manager_id FROM emp)) AS not_in_count, (SELECT COUNT(*) FROM emp e WHERE NOT EXISTS (SELECT 1 FROM emp m WHERE m.manager_id = e.id)) AS not_exists_count, COUNT(*) AS all_rows, COUNT(bonus) AS with_bonus, AVG(bonus) AS avg_bonus, AVG(COALESCE(bonus, 0)) AS avg_missing_as_zeroFROM emp;NOT EXISTS asks a different question ("is there a matching row?") which is
always true or false, so it finds the two employees. The same row shows how
aggregates treat NULL: COUNT(*) counts rows, COUNT(bonus) counts
non-null values, and AVG(bonus) divides by 2, not 4. If missing should mean
zero, say so with COALESCE(bonus, 0).
Sorting and uniqueness
In MySQL, NULL sorts before every value in ascending order and after
every value in descending order. There is no NULLS LAST clause, but
sorting on the expression col IS NULL first (0 before 1) pushes them to
the end. A UNIQUE index allows any number of NULLs, because no NULL
is equal to another.
CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(50) NULL, bonus INT, UNIQUE KEY uq_email (email));-- Both rows with a NULL email are accepted by the unique key.INSERT INTO emp VALUES (1, 'Ann', 'ann@x.io', 100), (2, 'Bob', NULL, NULL), (3, 'Cy', 'cy@x.io', 50), (4, 'Dee', NULL, NULL);SELECT name, email, bonus, ROW_NUMBER() OVER (ORDER BY bonus, name) AS asc_position, ROW_NUMBER() OVER (ORDER BY bonus IS NULL, bonus DESC, name) AS desc_nulls_lastFROM empORDER BY id;Common mistakes
NOT INagainst a nullable column. OneNULLin the subquery empties the result, as thenot_in_countof 0 shows. UseNOT EXISTS.a <> bto detect a change. When either side isNULLthe result isNULL, so changes to or fromNULLare missed. UseNOT (a <=> b).AVG(col)when missing should count as zero. It averages only the non-null values: 75 instead of 37.5 above.- Expecting
UNIQUEto stop a secondNULL. BothNULLemails were accepted; addNOT NULLif the value is required.
What interviewers ask
- Why does
WHERE id NOT IN (SELECT manager_id FROM emp)return no rows? The subquery contains aNULL, so eachNOT INis unknown rather than true; or filtering out fixes it.
Exercise · Pro
Whose bonus changed?
Exercises are free in the first 3 lessons of every course. From here on they are part of Pro and Sprint; the lesson and its runnable examples stay open.
Upgrade