What is this operator <=> in MySQL?
mysql, operators, spaceship-operator, sql
Solution
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
Problem
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.