MySQL NULL safe equal to operator

NULL safe equal to operator

MySQL null safe equal to operator performs an equality comparison like the equal to (=) operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

This function is useful in -

• It simplifies conditional logic, reducing the need for complex CASE statements or additional conditions to handle NULL values.
• It allows for direct comparison between two values, even if one or both of them are NULL. This is crucial for cases where NULL values might be present.
• Without the NULL-safe operator, comparisons with NULL would usually result in an unknown or NULL outcome.
• It ensures that comparisons involving NULL values return results as expected.
• In cases where you're joining tables and need to compare potentially NULL values, the NULL-safe equal to operator ensures that the join logic works as intended.
• It makes the code more readable and concise by avoiding the need for additional handling of NULL values in comparison operations.

Syntax:

<=>

MySQL Version: 8.0

Example: MySQL NULL safe equal to operator

The following MySQL statement compares if 1 is less than, equal to or greater than NULL; if NULL is less than, equal to or greater than NULL and if 3 is less than, equal to or greater than NULL.

Code:

SELECT NULL <=> 1, NULL <=> NULL, 3 <=> NULL;

Output:

mysql> SELECT NULL <=> 1, NULL <=> NULL, 3 <=> NULL;
+------------+---------------+------------+
| NULL <=> 1 | NULL <=> NULL | 3 <=> NULL |
+------------+---------------+------------+
|          0 |             1 |          0 | 
+------------+---------------+------------+
1 row in set (0.03 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: COALESCE()
Next: Equal operator(=)