Geek Answers Handbook

IN Keyword versus OR keyword

Does it really matter if I use one of the following conditions in a WHERE :

 WHERE [Process Code] = 1 AND ([Material ID] = 'PLT' OR [Material ID] = 'BMI') --or WHERE [Process Code] = 1 AND [Material ID] IN ('PLT', 'BMI')

Will there be a time that I would use instead of another?

+4
source share
4 answers

In the example you provided, they do the same (they will lead to the same execution plan). However, with the latest syntax (besides the shorter ones) you can do this:

 WHERE [Process Code] = 1 AND [Material ID] IN (SELECT ID FROM Material WHERE Type = @type)
+4
source

I can’t figure out what time I will use the first option. The second option is much cleaner and more expressive. And if it ever needs to be converted to a subsample, it will be easier to do.

+5
source

IN is just syntactic sugar for OR's bundle. IN usually preferable because it is much easier to read: it does not repeat the name of the variable, and you do not need to think about whether AND or OR a higher operator precedence.

+1
source

Query execution plans are usually the same anyway. You can check your expressions for zero ( ISNULL(@parm,'') or ISNULL([fieldname],'') , etc.) before starting the comparison.

Microsoft warns:

Any values ​​returned by a subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values ​​with IN or NOT IN may produce unexpected results.

+1
source

More articles:


All Articles