Amp; lt; amp; gt; operatorinMySQL Opera
Have you ever seen "<=>" in a SQL query while using MySQL? Does it mean less and equals to and greater? Actually if you consider it as the union of <= and =>, great, you are close to it. this is one form of equal operator in MySQL, it has the similar meaning to the = operator with some subtle difference.
According toMySQL documentation, <=> isNULL
-Safe equal. This operator performs an equal ity comparison like=
Operator, but returnsatherNULL
If both operands areNULL
, AndratherNULL
If one operand isNULL.
For example:
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;-> 1, 1, 0mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;-> 1, NULL, NULL
Next let's get to know more details about <=> operator in MySQL. To be noted this operator is not standard SQL operator, it's only available in MySQL.
Similarity
=
Operator
Like the regular=
Operator, two values are compared and the result is either (not equal) or (equal); in other words:'a' <=> 'b'
Yieldsand'a' <=> 'a'
Yields.
Difference
=
Operator
Unlike the regular=
Operator, valuesNULL
Don't have a special meaning and so it never yieldsNULL
As a possible outcome; so:'a' <=> NULL
YieldsandNULL <=> NULL
Yields.
Contrary=
, Whereby'a' = NULL
YieldsNULL
And evenNULL = NULL
YieldsNULL
; BTW, almost all operators and functions in MySQL work in this manner, because comparing againstNULL
Is basically undefined.
Usefulness
This is very useful for when both operands may containNULL
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 orNULL
Without having to change anything about the query.
Related operators
Besides<=>
There are also two other operators that can be used to compare againstNULL
, NamelyIS NULL
AndIS 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 specializations of MySQL's<=>
:
'a'ISNULL==>'a'<=>NULL'a'ISNOTNULL==>NOT('a'<=>NULL)
Based on this, your particle query (fragment) can be converted to the more portable:
WHERE p.name ISNULL