<=> operator in MySQL

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

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

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

WHERE p.name ISNULL