SQL NULL-SAFE EQUAL

In SQL, the NULL-SAFE EQUAL operator performs an equality comparison like the = operator. However, it can also accept NULL values and never returns NULL, instead always returning TRUE or FALSE. It uses the logic of an XNOR gate to determine the value returned. It is written as <=>.

This operator is useful in cases where XNOR-like logic is required for comparisons that can accept NULL values and always return TRUE or FALSE.

Syntax

SELECT column1
FROM table
WHERE column1 <=> value;

Note: The syntax can vary across different SQL flavors. In PostgreSQL, the operator is written as IS NOT DISTINCT FROM. In MariaDB/MySQL, it is written as <=>. Also, in SQLite, it is written as IS.

Example

The following example demonstrates the usage of the NULL-SAFE EQUAL operator:

SELECT NULL <=> 1, NULL <=> NULL, 3 <=> NULL, 2 <=> 2;

Copy to clipboard
Copy to clipboard

The above query returns the following result:

0, 1, 0, 1

Copy to clipboard
Copy to clipboard