Schedule Meeting

a

NULL comparisons in MariaDB, PostgreSQL, and SQLite

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

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/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

MySQL 8.0.33 : Quick Overview

MySQL 8.0.33 : Quick Overview

The MySQL Team has released MySQL 8.0.33 very recently on April 18, 2023. This release contains a number of bug fixes along with some interesting improvements. In this blog post, I'll cover the most important changes in MySQL 8.0.33. Variables can be set while...

The UUID data type in MariaDB

The UUID data type in MariaDB

MariaDB introduced the UUID data type in version 10.7. The first long-term support (LTS) version to include it is 10.11, which was declared stable in February 2023.

Overview of detailed slow query logging in MySQL 8: log_slow_extra

Overview of detailed slow query logging in MySQL 8: log_slow_extra

Every MySQL 8 minor release comes with a good number of bug fixes as well as exciting new features. MySQL 8.0.14 introduced the new log_slow_extra parameter. It is used to enable additional fields in the MySQL slow query log. They will help you get more information...

Services

0 Comments

Submit a Comment

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