Last Updated on

Any comparison involving at least one NULL
operand will return NULL
. This is expected, as it is implicit in how NULL
works. But somtetimes developers write verbose conditions to handle NULL
. For example if they want to consider NULLs 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.
Equality and inequality
The following operators are NULL
-safe equalities and inequalities. They behave like normal equality operators, but they consider NULL
as any other value. NULL
and NULL
are equal, NULL
and 1 are 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 |
MySQL / MariaDB
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
MySQL and PostgreSQL support the IS [NOT] TRUE
and IS [NOT] FALSE
operators. These have an interesting characteristic:
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)
As you can see, these operators are NULL
-safe.
MySQL booleans
The astute reader probably knows that MySQL doesn’t support a real BOOL
type. Actually, MySQL only supports 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 1 <> 2
. 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 |
+-----------+-------------+
MySQL also supports IS [NOT] UNKNOWN
, but that is just an alias for IS [NOT] NULL
.
See also
- How to concatenate strings in MySQL and MariaDB shows how to concatenate string in a NULL-safe way
- MySQL: IS NULL conditions and indexes discusses the optimisation of queries with
IS NULL
conditions in MySQL
Reference
- Just after publishing this post, I found a great summary of implicit type conversions in MySQL and PostgreSQL by Grabriela Ferrara: Your SQL IS NOT JavaScript (neither PHP)
Toodle pip,
Federico
Photo credit: link
Good write-up, I am regular visitor of one抯 web site, maintain up the nice operate, and It is going to be a regular visitor for a long time.