CODEWITHSUNDEEP
mysql
victorf
victorf

Reputation: 1048

Is there any diference between "!= NULL" and "IS NOT NULL" in MySQL?

Is there any difference between using "!= NULL" and using "IS NOT NULL"?

For example:

SELECT * FROM tbl_example WHERE a_field IS NOT NULL

and

SELECT * FROM tbl_example WHERE a_field != NULL

Upvotes: 1

Views: 97

Answers (2)

ASmith78
ASmith78

Reputation: 153

You shouldn't compare values to null, becouse efect of this operation isn't true neither false - it's always unknown.

To check is value null, you should always use is null

Upvotes: 3

TimWolla
TimWolla

Reputation: 32701

Yes, there is. != does not work properly with NULL1:

mysql> SELECT 1 != NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 != NULL | 1 IS NOT NULL |
+-----------+---------------+
|      NULL |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

BTW: != is not valid SQL, you should use the diamond operator <>.

1 In fact no comparison, except IS and IS NOT works.

Upvotes: 8

Related Questions