What is this operator <=> in MySQL?

In MySQL, the operator <=> is known as the 'NULL-safe equal' operator. It is used to compare two expressions, including those involving NULL values.

The standard equal operator = always returns NULL when any operand is NULL. This is because in SQL, NULL represents 'unknown', and any comparison involving unknown should return unknown, i.e., NULL.

However, the <=> operator differs in that it accounts for NULL in comparisons. If both operands are NULL, <=> returns TRUE. If one operand is NULL and the other is not, it returns FALSE. Only when both operands are non-NULL and equal does <=> return TRUE.

Example

Consider the following table users with two fields id and name, where the name field can contain NULL values.

id	name
1	Alice
2	NULL
3	Bob

Example query:

sql
SELECT * FROM users WHERE name <=> NULL;

This will return the row with id 2, as its name field is NULL.

In contrast, using the regular equal operator:

sql
SELECT * FROM users WHERE name = NULL;

This will return no rows because comparisons using the regular equal operator always return NULL when NULL values are present, not TRUE.

Therefore, the <=> operator is a very useful tool for handling data that includes NULL values, ensuring the accuracy and logical consistency of comparisons.