NULL comparisons in MariaDB, PostgreSQL, and SQLite

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 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.

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.

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

Did you like this article?