What's the difference between "is null " AND "<=> NULL"

isnull, mysql, null, sql

Solution

NEVER check for nulls using `foo = null` or `foo <> null` or `foo != null`

mysql> SELECT 1 <> NULL;
        -> NULL

Not even NULL is equal to NULL!

mysql> SELECT NULL = NULL;
        -> NULL

Instead use one of the following operators

The `<=>` is the Null-Safe Operator

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns `1` rather than NULL if both operands are NULL, and `0` rather than NULL if one operand is NULL.

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

On the other hand, IS NULL is a little more straight forward

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

Important: Read the IS NULL documentation to see how the `sql_auto_is_null` setting affects this operator.

See also: IS NOT NULL to test for values not equal to NULL.

You might be interested in COALESCE too.

Problem

What's the difference between `is null` and `<=> NULL` ? ``` mysql> SELECT * FROM param WHERE num is null; +-----+------+ | id | num | +-----+------+ | 8 | NULL | | 225 | NULL | +-----+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM param WHERE num<>NULL; Empty set (0.00 sec) mysql> SELECT * FROM param WHERE num<=>NULL; +-----+------+ | id | num | +-----+------+ | 8 | NULL | | 225 | NULL | +-----+------+ ``` difference in standards versions? I do not see the practical use of parameter `<=>` if it has `is null`

Original source