What does MySQL <=> operator do?

Question

What is the MySQL <=>?

Because the operator is a symbol it is hard to look for documentation. (Similar to the ternary operator ?: for programing languages that support them.)

I got it from an example in a book.

mysql> select null <=> null;
+---------------+
| null <=> null |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Answer 1

It's a null-safe comparison operator. And it's awesome.

What that means is if you're trying to query your database for some variable, like a string, that might sometimes be null, you want to use it. For example, if you try searching SELECT * FROM table WHERE x = NULL it will return nothing, but if you do SELECT * FROM table WHERE x <=> NULL it'll work.

Answer 2

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

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

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator%5Fequal-to

Answer 3

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator%5Fequal-to

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.