I’m working on code written by a previous developer and in a query it says,
WHERE p.name <=> NULL
<=> 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.
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
'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
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.
<=> 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 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
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
Answered By – Ja͢ck
Answer Checked By – Dawn Plyler (BugsFixing Volunteer)