Today's Question:  What does your personal desk look like?        GIVE A SHOUT

<=> operator in MySQL

  sonic0002        2014-03-24 06:23:22       3,907        2    

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 to MySQL documentation, <=> is 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.

For example:

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> 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 0 (not equal) or 1 (equal); in other words: 'a' <=> 'b' yields 0 and 'a' <=> 'a' yields 1.

Difference with = operator

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.

Contrary to =, whereby 'a' = NULL yields NULL and even NULL = NULL yields NULL; BTW, almost all operators and functions in MySQL work in this manner, because comparing against NULL is basically undefined.

Usefulness

This is very useful for 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 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

MYSQL  NULL SAFE  <=> 

Share on Facebook  Share on Twitter  Share on Weibo  Share on Reddit 

  RELATED


  2 COMMENTS


Charlie [Reply]@ 2014-03-31 05:48:27
Everything composed was very logical. However, what about this? what if you added a little information? I mean, I don't wish to tell you how to run your blog, however what if you added something to possibly get people's attention? I mean <=> operator in MySQL is kinda boring. You might look at Yahoo's home page and see how they create article headlines to get people to open the links. You might try adding a video or a related pic or two to grab people interested about what you've got to say. In my opinion, it could make your posts a little bit more interesting.
sonic0002 [Reply]@ 2014-03-31 09:50:28

Thank you for your advice. We will consider more about the titles for our article titles.We will endeaver to make our titles more interestng and attractive in the future.