What is this operator <=> in MySQL?
MysqlSqlOperatorsSpaceship OperatorMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
TL;DR
It's the NULL
safe equal operator.
Like the regular =
operator, two values are compared and the result is either 0
(not equal) or 1
(equal); in other words: 'a' <=> 'b'
yields 0
and 'a' <=> 'a'
yields 1
.
Unlike the regular =
operator, values of NULL
don't have a special meaning and so it never yields NULL
as a possible outcome; so: 'a' <=> NULL
yields 0
and NULL <=> NULL
yields 1
.
Usefulness
This can come in useful when both operands may contain NULL
and you need a consistent comparison result between two columns.
Another use-case is with prepared statements, for example:
... WHERE col_a <=> ? ...
Here, the placeholder can be either a scalar value or NULL
without having to change anything about the query.
Related operators
Besides <=>
there are also two other operators that can be used to compare against NULL
, namely IS NULL
and IS NOT NULL
; they're part of the ANSI standard and therefore supported on other databases, unlike <=>
, which is MySQL-specific.
You can think of them as specialisations of MySQL's <=>
:
'a' IS NULL ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)
Based on this, your particular query (fragment) can be converted to the more portable:
WHERE p.name IS NULL
Support
The SQL:2003 standard introduced a predicate for this, which works exactly like MySQL's <=>
operator, in the following form:
IS [NOT] DISTINCT FROM
The following is universally supported, but is relative complex:
CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
THEN 1
ELSE 0
END = 1
Solution 2 - Mysql
is <=> NULL-safe equal to operator
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.
See here for the documentation
Sample :
you should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)
SELECT *
FROM table
WHERE YourColumn IS NOT NULL;
can also negate the null safe equality operator but this is not standard SQL.
SELECT *
FROM table
WHERE NOT (YourColumn <=> NULL);
Solution 3 - Mysql
It is the NULL-safe equal to operator
<=> Operator is used to compare NULL values with the fields. If normal =(equals) Operators return NULL if one of the comparison value is NULL. With <=> operator returns true or false. <=> Operator is same as IS NULL.
From the manual:-
> <=>
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
Edit:-(Although very late to add one important side note mentioning NOT <=> as well)
On a side note:-
NOT <=>
There is one more point NOT <=> which is used to compare NULL values with the fields. If normal != or <> (not equals) Operators return NULL if one of the comparison value is NULL. With NOT applied to <=> operator returns true or false. NOT applied to <=> Operator is same as IS NOT NULL.
Example:-
SELECT NULL != NULL, //--Result is NULL
NOT NULL <=> NULL, //--Result is 0
NULL IS NOT NULL; //--Result is 0
Solution 4 - Mysql
<=>
is MySQL's null-safe "equal to" operator. From the manual:
>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
Solution 5 - Mysql
<=>
is the NULL-safe equal operator. It is equivalent to the standard SQL is not distinct from
operator. Its behavior is best explained via truth table:
a | b | a <=> b |
---|---|---|
1 | 0 | false |
1 | 1 | true |
1 | null | false* |
null | null | true* |
* Notice that this operator will never return unknown (represented by null) unlike other operators.
Solution 6 - Mysql
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
It significance:
When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null.
The Equality operator(<=>) which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:
eg
SELECT NULL <=> NULL -- 1
SELECT TRUE <=> TRUE -- 1
SELECT col1 <=> col2 FROM myTable
Solution 7 - Mysql
From the MySQL documentation:
>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.
An example using the <=>
operator would be:
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
Which would return:
1, 1, 0
An example of the regular =
operator would be:
SELECT 1 = 1, NULL = NULL, 1 = NULL;
Which would return:
1, NULL, NULL
The <=>
operator is very similar to the =
operator, except <=>
will never return NULL
Solution 8 - Mysql
It is the NULL - Safe Equal to operator. Check description.
Solution 9 - Mysql
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 |
Solution 10 - Mysql
The difference between operand1=operand2
and operand1<=>operand2
is that the prior will return null
while any of the two operands are null.
But the latter return 1
or 0
depends on the operands' value even if null.
As a contrast:
select null = null
, returnnull
; butselect null <=> null
,return1
select null = 'abc'
,returnnull
; butselect null <=> 'abc'
,return0