What is this operator <=> in MySQL?

Question

I'm working on code written by a previous developer and in a query it says,

WHERE p.name <=> NULL

What does <=> mean in this query? Is it something equal to =? Or is it a syntax error?

But it is not showing any errors or exceptions. I already know that <> = != in MySQL.

Answer 1

<=> is the NULL-safe equal operator. a <=> b is same as writing:

CASE
    WHEN a IS NULL AND b IS NULL THEN 1 -- both operands null then 1
    WHEN a IS NULL OR  b IS NULL THEN 0 -- one operand is null then 0
    ELSE a = b                          -- else behave like normal = operator
END

And sorry, I could not find one good reason to use this operator instead of AND/OR IS (NOT) NULL. Your example for example, WHERE p.name <=> NULL is same as WHERE p.name IS NULL.

Answer 2

mysql> SELECT * FROM t JOIN t2 WHERE t2.ids = t.ids;
+----+------+----+------+
| id | ids  | id | ids  |
+----+------+----+------+
|  1 |    1 |  1 |    1 |
|  2 |    2 |  2 |    2 |
|  5 |    6 |  5 |    6 |
|  6 |    7 |  6 |    7 |
+----+------+----+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t JOIN t2 WHERE t2.ids <=> t.ids;
+----+------+----+------+
| id | ids  | id | ids  |
+----+------+----+------+
|  1 |    1 |  1 |    1 |
|  2 |    2 |  2 |    2 |
|  3 | NULL |  3 | NULL |
|  4 | NULL |  3 | NULL |
|  3 | NULL |  4 | NULL |
|  4 | NULL |  4 | NULL |
|  5 |    6 |  5 |    6 |
|  6 |    7 |  6 |    7 |

Answer 3

It is the NULL - Safe Equal to operator. Check description.