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
'a' <=> 'a' yields
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
NULL <=> NULL yields
'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.
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.
<=> there are also two other operators that can be used to compare against
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
Based on this, your particular query (fragment) can be converted to the more portable:
WHERE p.name ISNULL