What is this operator <=> in MySQL?

Question

I'm working on code written by a previous developer and in a query it says,

WHERE p.name <=> NULL

What does <=> mean in this query? Is it something equal to =? Or is it a syntax error?

But it is not showing any errors or exceptions. I already know that <> = != in MySQL.

Answer 1

It is the NULL-safe equal to operator

<=> Operator is used to compare NULL values with the fields. If normal =(equals) Operators return NULL if one of the comparison value is NULL. With <=> operator returns true or false. <=> Operator is same as IS NULL.

From the manual:-

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

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Edit:-(Although very late to add one important side note mentioning NOT <=> as well)

On a side note:-

NOT <=>

There is one more point NOT <=> which is used to compare NULL values with the fields. If normal != or <> (not equals) Operators return NULL if one of the comparison value is NULL. With NOT applied to <=> operator returns true or false. NOT applied to <=> Operator is same as IS NOT NULL.

Example:-

SELECT NULL != NULL,         //--Result is NULL
   NOT NULL <=> NULL,        //--Result is 0
   NULL IS NOT NULL;         //--Result is 0

Answer 2

From the MySQL documentation:

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.

An example using the <=> operator would be:

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

Which would return:

1, 1, 0

An example of the regular = operator would be:

SELECT 1 = 1, NULL = NULL, 1 = NULL;

Which would return:

1, NULL, NULL

The <=> operator is very similar to the = operator, except <=> will never return NULL

Answer 3

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' IS NULL     ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)

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

WHERE p.name IS NULL

Answer 4

<=> is MySQL's null-safe "equal to" operator. From the manual:

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.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Answer 5

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.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

It significance:

When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null.

The Equality operator(<=>) which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:

eg

 SELECT NULL <=> NULL -- 1
 SELECT TRUE <=> TRUE -- 1
 SELECT col1 <=> col2 FROM myTable

Answer 6

is <=> NULL-safe equal to operator

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.

See here for the documentation

Sample :

you should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);