1
  1. select field from table where field = 'value'
  2. select field from table where field in ('value')

The reason I'm asking is that the second version allow me to use the same syntax for null values, while in the first version I need to change the condition to 'where field is null'...

yonayaha
  • 47
  • 7
  • You are comparing apples and oranges. – dani herrera Jul 31 '14 at 13:25
  • 1
    What happens when you do `in ('value', null)`? do you get rows where `field` is `null` ? – Lasse V. Karlsen Jul 31 '14 at 13:25
  • when you are comparing null values to a field without using `IN` you need to use `IS NULL` – Edper Jul 31 '14 at 13:32
  • Your question is similar to this [question](http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) – Edper Jul 31 '14 at 13:34
  • possible duplicate of [SQL difference between IN and OR in WHERE](http://stackoverflow.com/questions/9024594/sql-difference-between-in-and-or-in-where) – Murtaza Jul 31 '14 at 13:37
  • By the way what exactly RDBMS did you use? MySQL, MS SQL, PostGre? Because using IN clause does not return anything when NULL is used. See [here](http://sqlfiddle.com/#!3/d72573/6) and [here](http://sqlfiddle.com/#!2/d7257/2). – Edper Jul 31 '14 at 13:46
  • @Edper: the database is called Postgres or PostgreSQL. Never PostGre (https://wiki.postgresql.org/wiki/Identity_Guidelines) –  Jul 31 '14 at 14:14
  • @a_horse_with_no_name: I realize that the moment I post it but did not mind to change it. You're correct by the way. Thanks. – Edper Jul 31 '14 at 14:16
  • I though I can use `in ('value', null)` but it seems not... – yonayaha Aug 05 '14 at 12:41

3 Answers3

2

When you are comparing a field to a null like field_name=NULL you are comparing to a known data type from a field say varchar to not only an unknown value but also an unknown data type as well, that is, for NULL values. When comparison like field_name=NULL again implies therefore a checking of data type for both and thus the two could not be compared even if the value of the field is actually NULL thus it will always result to false. However, using the IS NULL you are only comparing for the value itself without the implied comparison for data type thus it could result either to false or true depending on the actual value of the field.

See reference here regarding the issue of NULL in computer science and here in relation to the similarity to your question.

Now, for the IN clause (i.e. IN(NULL)) I don't know what RDBMS you are using because when I tried it with MS SQL and MySQL it results to nothing.

See MS SQL example and MySQL example.

Community
  • 1
  • 1
Edper
  • 9,144
  • 1
  • 27
  • 46
0

There is no difference in your example. The second, slightly longer, query is not usually used for a single value, it is usally seen for multiple values, such as 

select field from table where field in ('value1', 'value2')
Mark Nash
  • 184
  • 10
  • Mark: I think the OP was not basically asking the difference between the regular `WHERE` and one using `IN` statement. The OP was asking why `field_here=null` is different than `IS NULL`. – Edper Jul 31 '14 at 13:37
-1

yes there is difference in both this queries. In first statment you can insert only 1 value in where clause "where field = 'value'" but in second statement in where field you can insert many values using IN clause "where field in (value1,value2..)" Examples:

1) select field from table where field ='value1';

2) select field from table where field in ('value1', 'value2')

To check null values 

SELECT field
FROM tbl_name
WHERE 
(field IN ('value1', 'value2', 'value3') OR field IS NULL)
user3844830
  • 44
  • 1
  • 2
  • 11
  • 1
    This not the expected answer, we need to know the difference in execution plan, say if i use = operator what is the difference and if i use IN clause what is the difference. – Murtaza Jul 31 '14 at 13:34
  • @Murtaza Can you please check i have corrected answer now..And if it is not so u can update my answer. – user3844830 Jul 31 '14 at 13:46