NULL comparisons in MariaDB, PostgreSQL, and SQLite

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.

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?