Last updated on 26 May 2022
Any comparison involving at least one
NULL
operand will return
NULL
. This is an implicit characteristics of
NULL
. But sometimes developers write too verbose conditions to handle
NULL
. For example when they want to consider
NULL
s equal they often write:
WHERE col = val OR (col IS NULL AND val IS NULL)
This condition is unnecessarily verbose and, most importantly, it’s possible that it will not use an index. Most DBMSs offer more specific ways to check values equality or inequality in a
NULL
-safe way.

NULL-safe equality operators
Some operators are
NULL
-safe equalities / inequalities. They behave like normal equality operators, but they consider
NULL
as any other value. They consider
NULL
and
NULL
equal,
NULL
and
FALSE
different. These operators always return
TRUE
or
FALSE
, and never return
NULL
.
The following truth table shows this graphically.
FALSE | TRUE | NULL | |
---|---|---|---|
FALSE | 1 | 0 | 0 |
TRUE | 0 | 1 | 0 |
NULL | 0 | 0 | 1 |
NULL
-safe operators are:
MariaDB / MySQL
-
WHERE a <=> b
-
WHERE NOT (a <=> b)
PostgreSQL
-
WHERE a IS NOT DISTINCT FROM b
-
WHERE a IS DISTINCT FROM b
SQLite
-
WHERE a IS b
-
WHERE a IS NOT b
Are you considering to always use this syntax instead of
=
to be sure you handle
NULL
correctly? Please don’t:
NULL
-safe conditions may prevent a query from using an index.
IS NOT operators
MariaDB, MySQL and PostgreSQL support the
IS [NOT] TRUE
and
IS [NOT] FALSE
operators. These are
NULL
-safe operators that consider
TRUE
,
FALSE
and
NULL
as regular, distinct values:
postgres=# SELECT NULL IS TRUE, NULL IS FALSE;
?column? | ?column?
----------+----------
f | f
(1 row)
postgres=# SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
?column? | ?column?
----------+----------
t | t
(1 row)
MariaDB / MySQL booleans
The astute reader probably knows that MariaDB and MySQL don’t support a real
BOOL
type. Actually, MariaDB/MySQL only support some synonyms:
BOOLEAN = TINYINT UNSIGNED
BOOL = TINYINT UNSIGNED
FALSE = 0
TRUE = 1
This has puzzling consequences, for example:
mysql> SELECT 2 = TRUE;
+----------+
| 2 = TRUE |
+----------+
| 0 |
+----------+
This is not a bug. On the contrary, it is perfectly consistent, because
2 <> 1
. Nevertheless, from a user’s point of view, this result makes no sense.
Fortunately,
IS TRUE
and
IS FALSE
operators do what you would expect:
mysql> SELECT 2 IS TRUE, '' IS FALSE;
+-----------+-------------+
| 2 IS TRUE | '' IS FALSE |
+-----------+-------------+
| 1 | 1 |
+-----------+-------------+
MariaDB/MySQL also support
IS [NOT] UNKNOWN
, but that is just an alias for
IS [NOT] NULL
.
See also
- MariaDB/MySQL: IS NULL conditions and indexes discusses the optimisation of queries with
IS NULL
conditions in MariaDB and MySQL.
Conclusions
To master advanced SQL and query optimisation, consider our SQL Optimisation Training for teams.
Federico Razzoli