<=> operator in MySQL_MySQL

Have you ever seen "<=>" in a SQL query while using MySQL? Does it mean less and equals to and greater than? 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 equality comparison like the=operator, but returnsrather thanNULLif both operands areNULL, andrather thanNULLif 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 with=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 with=operator

Unlike the regular=operator, values ofNULLdon't have a special meaning and so it never yieldsNULLas a possible outcome; so:'a' <=> NULLyieldsandNULL <=> NULLyields.

Contrary to=, 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 particular query (fragment) can be converted to the more portable:

WHERE p.name ISNULL