Schedule Meeting

a

NULL comparisons in MariaDB, PostgreSQL, and SQLite

by | Sep 6, 2021 | MariaDB, MySQL, Other Databases, PostgreSQL | 0 comments

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.

Sometimes even Blixy is confused when it comes to NULL comparisons.

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.

 FALSETRUENULL
FALSE100
TRUE010
NULL001

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

Conclusions

To master advanced SQL and query optimisation, consider our SQL Optimisation Training for teams.

Federico Razzoli

Did you like this article?

All content in this blog is distributed under the Creative Commons Attribution 4.0 International license (CC BY 4.0). You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by/4.0/

[/et_pb_column]
About Federico Razzoli
Federico is Vettabase Ltd founder, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems.

Recent Posts

A summary of MariaDB 10.10: INET4 type, RANDOM_BYTES() and more

A summary of MariaDB 10.10: INET4 type, RANDOM_BYTES() and more

In my first post in 2023, I want to give you a summary of MariaDB 10.10. Some may argue that I'm a bit late because it was released last November, and versions 10.11 and 11.0 have been announced since then... All that is correct, but 10.10 is the latest stable...

How to compose strings in MariaDB

How to compose strings in MariaDB

MariaDB supports several ways to compose strings dynamically. Some of them might be well-known, others aren’t familiar to you. This article covers all the ways to compose strings.

Hints to optimise queries with a LIKE comparison

Hints to optimise queries with a LIKE comparison

In SQL, using the LIKE operator is a powerful way to find strings that match a certain pattern. It's suitable for most use cases, thanks to its two jolly characters: _ means any one character. % means any sequence of zero or more characters. However, many queries out...

Services

0 Comments

Submit a Comment

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

How to compose strings in MariaDB

How to compose strings in MariaDB

MariaDB supports several ways to compose strings dynamically. Some of them might be well-known, others aren’t familiar to you. This article covers all the ways to compose strings.

read more

How to compose strings in MariaDB

How to compose strings in MariaDB

MariaDB supports several ways to compose strings dynamically. Some of them might be well-known, others aren’t familiar to you. This article covers all the ways to compose strings.

read more