What purpose of the <=> operator in MySQL?

Question:

Still doing a series of tests on MySQL I noticed that the <=> operator worked equivalent to = for the tested case, this one below:

SELECT * FROM `client` WHERE avatar <=> NULL

This returned the only two items it has in the base, both for <=> and for = . Another test I did was with the <> , which returns all items that the avatar column is different from NULL , for this select above.

What purpose of the <=> operator in MySQL? Is there really any equivalence with the equality operator? When should it be used?

Answer:

It's the "equal" that handles nulls without causing any problems. So null is treated as a normal value but different from all others. The normal behavior is that an operation that involves a NULL value always results in NULL It changes this.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

I put it on GitHub for future reference .

Documentation .

In your example it will give true or false according to the avatar value that is expected not to be null. Using the simple equality operator would always result in NULL .

Scroll to Top