j-1064772 (1/14/2014)
[font="Comic Sans MS"]So the optimizer IS actually using the equivalent from Boolean algebra ?[/font]As I wrote before:
[font="Courier New"]WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)[/font]
[font="Comic Sans MS"]Using Boolean algebra the last expression should yield the same results[/font]
[font="Courier New"]WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)[/font]
What you are struggling here with is the concept of NULL. Remember that NULL is not a value placeholder. That is why nothing can equal NULL.
NULL is a representation for a missing value. This is why your check will not return the results you are expecting.
Keeping with examples of not requiring a table take a look at this.
declare @ProductID int = 10
select 'Yes'
--where 1 not in (1, null)
WHERE NOT (@ProductID = 1 OR @ProductID = 2 OR @ProductID = 3 OR @ProductID = 4 OR @ProductID = NULL)
Essentially this is looking for any of the known values OR an unknown value. It can't simultaneously be any of a given known values OR an unknown value.
Take the above example and with a slight modification it will do exactly what you are asking.
declare @ProductID int = 10
select 'Yes'
WHERE NOT (@ProductID = 1 OR @ProductID = 2 OR @ProductID = 3 OR @ProductID = 4 OR @ProductID is NULL)
You shorten this to use NOT IN like you were but you have another slight change.
declare @ProductID int = 10
select 'Yes'
where @ProductID not in (1, 2, 3, 4) OR @ProductID IS NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/