What does “<=>” in MySQL mean?

What does <=> in MySQL mean and do?

Answers


The manual says it all:

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 NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

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

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

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

mysql> 

It's the NULL-safe equal operator.

The difference between <=> and = is when one or both of the operands are NULL values. For example:

NULL <=> NULL gives True
NULL = NULL   gives NULL

Here is the full table for the <=> comparison of values 1, 2 and NULL:

     |  1      2    NULL
-----+-------------------
1    | True   False False
2    | False  True  False
NULL | False  False True

Compare to the ordinary equality operator:

     |  1      2    NULL
-----+-------------------
1    | True   False NULL
2    | False  True  NULL
NULL | NULL   NULL  NULL

<=> is a so called NULL-safe-equality operator.

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

SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL

NULL-safe equal to operator

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to


It's the same as SQL standard keyword DISTINCT

SELECT * FROM somewhere WHERE `address1` is not distinct from `address2`

Need Your Help

Heroku PostgreSQL Crane DB vs Linode 1GB with PostgreSQL installed

postgresql heroku linode heroku-postgres

I'm trying to decide between using the Heroku Crane PostgreSQL database ($50/month - https://postgres.heroku.com/pricing) or setting up a Linode 1GB Ram / 8 CPU / 48GB Storage / 2TB Transfer instance

JMOL pdb files not being served

java iis asp.net-mvc-2

I am building an asp.net mvc web site which needs to host some JMOL content.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.