<=> operator in MySQL

5,530 2
Summary

The operator in MySQL offers a NULL-safe equality comparison, providing a consistent 0 or 1 result even when operands are NULL. Unlike the standard = operator, which returns NULL for comparisons involving NULL (e.g., NULL = NULL), evaluates NULL NULL as 1 and value NULL as 0. This behavior is highly beneficial for scenarios where columns might contain NULLs, such as in WHERE clauses or prepared statements, ensuring predictable comparison outcomes. Developers should note that this operator is a MySQL-specific extension, with IS NULL and IS NOT NULL serving as the ANSI standard and more portable alternatives.

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 <=>

  RELATED

  COMMENTS

2
Charlie
Mar 31, 2014 at 5:48 am
Reply
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
Mar 31, 2014 at 9:50 am
Reply

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.