Last Updated on August 16, 2019

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.

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

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