What is the operator <=> in MySQL?

Here are the usages of the <=> operator in MySQL.

Case 1

This operator is similar to = operator i.e. when the value is equal then the result will become true(1), otherwise false(0).

In the first case both = and <=> operators work same.

Case 2

Whenever we compare any value with NULL then the <=> operator gives the value 0 and when we compare with NULL <=> NULL, then it returns 1.

While in case of = operator, this does not happen. Whenever we compare any value with NULL, it returns NULL. If we compare NULL with NULL, then only NULL is returned.

Here is an example for both the cases discussed above. The query is as follows −

mysql> SELECT 10 <=> 10, NULL <=> NULL, 10 <=> NULL;

Here is the output.

+-----------+---------------+-------------+
| 10 <=> 10 | NULL <=> NULL | 10 <=> NULL |
+-----------+---------------+-------------+
|         1 |              1|            0|
+-----------+---------------+-------------+
1 row in set (0.00 sec)

Look at the above output, NULL <=> NULL returns 1, not NULL.

Let us now see an example for = operator. The query is as follows −

mysql> SELECT 10 = 10, NULL = NULL, 10 = NULL;

Here is the output.

+---------+-------------+-----------+
| 10 = 10 | NULL = NULL | 10 = NULL |
+---------+-------------+-----------+
|       1 |        NULL |      NULL |
+---------+-------------+-----------+
1 row in set (0.00 sec)

Look at the above output, NULL = NULL returns NULL.