Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is 'IS DISTINCT FROM' a real MySQL operator?

In one book I see this syntax:

SELECT * FROM inw WHERE id IS DISTINCT FROM 4;

But I get an error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT FROM 4' at line 1

It's an alternative for:

mysql> SELECT * FROM inw WHERE id is null OR id <> 4;
+------+
| id   |
+------+
| NULL |
| NULL |
|    3 |
+------+

Is 'IS DISTINCT FROM' a real MySQL operator?

like image 457
Michael Phelps Avatar asked Dec 25 '22 01:12

Michael Phelps


1 Answers

is distinct from is defined in the SQL:2003 standard and is a null-safe operator to compare two values.

MySQL supports a "null safe equals" operator: <=>. If that is negated, you get the same behaviour. (the <=> corresponds to is not distinct from)

SELECT * 
FROM inw 
WHERE not id <=> 4;

SQLFiddle: http://sqlfiddle.com/#!2/0abf2a/3

like image 166
a_horse_with_no_name Avatar answered Jan 05 '23 00:01

a_horse_with_no_name