<=> operator in MySQL_MySQL

Opera

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

Besidesthere 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