& Amp; lt; = & amp; gt; operatorinMySQL_MySQL

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 returnsatherNULLIf both operands areNULL, AndratherNULLIf 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, valuesNULLDon't have a special meaning and so it never yieldsNULLAs a possible outcome; so:'a' <=> NULLYieldsandNULL <=> NULLYields.

Contrary=, Whereby'a' = NULLYieldsNULLAnd evenNULL = NULLYieldsNULL; BTW, almost all operators and functions in MySQL work in this manner, because comparing againstNULLIs basically undefined.

Usefulness

This is very useful for when both operands may containNULLAnd 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 orNULLWithout having to change anything about the query.

Related operators

Besides<=>There are also two other operators that can be used to compare againstNULL, NamelyIS NULLAndIS 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