NULL comparisons in MySQL, PostgreSQL, and SQLite

NULL confuses truth values, creating an unknown anomaly

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.

 FALSETRUENULL
FALSE100
TRUE010
NULL001

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

Reference

Toodle pip,
Federico

Photo credit: link

Leave a Reply

Your email address will not be published. Required fields are marked *