What is MySQL NULL-safe equal operator and how it is different from comparison operator?

MySQLMySQLi Database

MySQL NULL-safe equal operator, equivalent to standard SQL IS NOT DISTINCT FROM operator, performs an equality comparison like = operator. Its symbol is <=>. It performs differently from the comparison operators in the case when we have NULL as both the operands. Consider the following examples to understand NULL-safe operator along with its difference with comparison operator −

mysql> Select 50 <=> 50, NULL <=> NULL, 100 <=> NULL;
+-----------+---------------+--------------+
| 50 <=> 50 | NULL <=> NULL | 100 <=> NULL |
+-----------+---------------+--------------+
|         1 |             1 |            0 |
+-----------+---------------+--------------+
1 row in set (0.00 sec)

mysql> Select 50 = 50, NULL = NULL, 100 = NULL;
+---------+-------------+------------+
| 50 = 50 | NULL = NULL | 100 = NULL |
+---------+-------------+------------+
|       1 |        NULL |       NULL |
+---------+-------------+------------+
1 row in set (0.00 sec)
raja
Published on 21-Feb-2018 14:08:20
Advertisements