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

课游记AI

AI原生学习产品

70

查看详情

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:

<code>...WHERE col_a <=>?...</code>

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:

<code>'a'ISNULL==>'a'<=>NULL'a'ISNOTNULL==>NOT('a'<=>NULL)</code>

Based on this, your particular query (fragment) can be converted to the more portable:

<code>WHERE p.name ISNULL</code>