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

TL;DR

It's the NULL safe equal 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.

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.

Usefulness

This can come in useful 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 specialisations 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

Support

The SQL:2003 standard introduced a predicate for this, which works exactly like MySQL's <=> operator, in the following form:

IS [NOT] DISTINCT FROM 

The following is universally supported, but is relative complex:

CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
     THEN 1
     ELSE 0
END = 1