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

Asked

Viewed 12,629 times

10

I’ve always wondered why we should 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 lines except when campo is not null.

Example:

SELECT * FROM tabela WHERE campo <> NULL

Displays nothing.

Observing: The first time I tried to make that comparison was with != and also did not work. Because the functionality of these operators are similar.

mysql query null
  • 3

    SQL understands that null cannot be compared and <> is the comparison operator. A comparison of a value with null results in "unknown", hence the result is not what you expect. As what is being compared with null or the type expected for the result of the comparison, the bank will have a specific behaviour. The right way (ANSI SQL default) to check if a field is null is is null (or is not null for denial).

  • 2

    I’ve seen people try to do != NULL, but with <> was the first time...

  • 2

    @Sneepsninja did not understand. Both operators (!= and <>) are supported by Mysql and I believe that <> is the ANSI SQL standard.

  • 1

    I am saying that the first time in my life I tried to make this query (of the question) I did so: SELECT * FROM teste WHERE x != NULL; and went wrong. The question was in the sense of knowing why this comparison does not work and the NOT NULL works

  • @Caffé the equivalent operator to compare the NULL would be like this spine <=> NULL and no <> NULL da uma lida ai http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html

  • Funny that @Sneepsninja! How something can be <=> (smaller or equal to or greater) ?

  • *opa para ser igual a NULL é <=> http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to

  • to fetch the nonnulls would have to gambiarrar same type NOT ( column <=> NULL);

  • @Sneepsninja What I didn’t understand was the fun part of trying '<>' to test if the column is "different from", since this is a common operator for this test. In any case, the <=> (Mysql special feature) is not the same as IS NOT NULL.

  • @Caffé when we filter like this coluna <> null or so coluna != null does not give the same result as coluna is not null, at least not in Mysql

  • @Sneepsninja Yes, it is there in my first comment on this question :-) I believe that currently coluna <> null does not work in any bank.

  • @Caffé... rsrsrsrsrsr so the 'fun' part is that I had never seen someone try to seek 'not null' values like this <> null only that :D

  • 2

    Related: Why NULL values are not selected?

Show 8 more comments

1 answer

16


You cannot use arithmetic comparison operators to validate the NULL for they will always return NULL. To verify a value NULL use to operators IS NULL and IS NOT NULL.

An example:

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

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: https://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Browser other questions tagged mysql query null

You are not signed in. Login or sign up in order to post.