CODEWITHSUNDEEP
mysqlsqlnullisnull
Michael Phelps
Michael Phelps

Reputation: 3591

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

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

Upvotes: 3

Views: 199

Answers (4)

zloctb
zloctb

Reputation: 11186

What's logic ?

mysql> SELECT NULL AND false;
+----------------+
| NULL AND false |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT NULL AND TRUE;
+---------------+
| NULL AND TRUE |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

Theoretical difference

<=> Operator

<=> is a safe null-comparison operator. That means you may use it and do not worry if you'll compare with NULL - it will behave properly. To illustrate, here is a simple query:

mysql> SELECT v, v<=>NULL, v<=>1, v<=>0 FROM test;
+------+----------+-------+-------+
| v    | v<=>NULL | v<=>1 | v<=>0 |
+------+----------+-------+-------+
|    1 |        0 |     1 |     0 |
| NULL |        1 |     0 |     0 |
+------+----------+-------+-------+
2 rows in set (0.00 sec)

So what <=> does - is normal comparison, with paying attention is one or two compared operands are NULL.

IS NULL

On the other hand, IS NULL is very similar. It will check if checked argument is NULL or not. But - no, it's not exactly same as using <=> - at least, because IS NULL will return boolean value:

mysql> SELECT v, v IS NULL FROM test;
+------+-----------+
| v    | v IS NULL |
+------+-----------+
|    1 |         0 |
| NULL |         1 |
+------+-----------+
2 rows in set (0.00 sec)

How they are equivalent

But - yes, we can replace <=> with IS NULL, using IF. That will be done with:

mysql> SELECT v, IF(v IS NULL, 1, 0) AS `v<=>NULL`, IF(v IS NULL, 0, v=1) AS `v<=>1`, IF(v IS NULL, 0, v=0) AS `v<=>0` FROM test;
+------+----------+-------+-------+
| v    | v<=>NULL | v<=>1 | v<=>0 |
+------+----------+-------+-------+
|    1 |        0 |     1 |     0 |
| NULL |        1 |     0 |     0 |
+------+----------+-------+-------+
2 rows in set (0.00 sec)

Thus, <=> is equivalent for combination of IF, IS NULL and plain comparison.

Practical difference

I already said, that <=> can be replaced with IS NULL and IF - but <=>, actually, have one great benefit. It may be used safely in prepared statements. Imagine that we want to check some condition with incoming value. Using <=> we can do this, using prepared statement:

mysql> PREPARE stmt FROM 'SELECT * FROM test WHERE v<=>?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

And now we can just do not care if we'll pass NULL or not - it will work properly:

mysql> SET @x:=1;
Query OK, 0 rows affected (0.03 sec)

mysql> EXECUTE stmt USING @x;
+------+
| v    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Or with NULL:

mysql> SET @x:=NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt USING @x;
+------+
| v    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

That, of course, will be same for all drivers, which are relying on prepared statements (such as PDO for PHP, for example).

Well. we can replace that with IS NULL - but <=> is a great thing to use in this case, so we can have our prepared statement short and clean.

Upvotes: 4

Strawberry
Strawberry

Reputation: 33945

In practice, they're logically identical, so the following will both produce the same result...

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

But in the following situation, one will throw an error while the other will not...

SELECT 1 <=> 1;
SELECT 1 IS 1;

So, 'IS NULL' can only be used to test for 'nullness'. 'IS' cannot be employed except in conjunction with 'NULL'. "<=>" can be used in a broader range of contexts making it (in my view) extremely useful.

Upvotes: 2

maček
maček

Reputation: 77778

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.

Upvotes: 9

Related Questions