Why should we use "IS NOT NULL" instead of "NULL"?

Question

Why should we use "IS NOT NULL" instead of "NULL"?

Navigation

#1 by (12 votes)

6

I always asked myself this: Why should we use IS NOT NULL instead of <> NULL ? For when I do as in the second case, no result is found.

Example:

SELECT * FROM tabela WHERE campo IS NOT NULL

Displays all rows, except when campo is not null.

Example:

SELECT * FROM tabela WHERE campo <> NULL

Does not display anything.

Comment : The first time I tried to make this comparison was != and it did not work either. Because the functionality of these operators are similar.

mysql query null

asked by anonymous 18.08.2015 / 22:24

1 answer

Txt file not found when generating .jar file How big is the line break?

score 12 · Accepted Answer

You can not use arithmetic comparison operators to validate the NULL because they will always return NULL . To check a NULL value, use the IS NULL and IS NOT NULL operators.

An example:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

In MySQL, 0 (zero) and NULL are considered as FALSE , and all the rest as TRUE . The default value for TRUE is 1 .

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Reading tip: link