j-1064772 (1/14/2014)

---

[font="Comic Sans MS"]Thank you all for your inputs, I now understand what INTERSECT and EXCEPT bring to the party.

I am however still trying to understand why the following occurs:[/font]

[font="Comic Sans MS"]I checked that[/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails (no record returned because of the extra null)[/font] [font="Comic Sans MS"]as opposed to[/font] [font="Courier New"]IN[/font] [font="Comic Sans MS"]which does work.[/font]

[font="Courier New"]WHERE ProductID IN (1, 2, 3, 4, NULL)[/font] [font="Comic Sans MS"]translates as[/font]

[font="Courier New"]WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL[/font]

[font="Courier New"]WHERE ProductID NOT IN (1, 2, 3, 4, NULL)[/font][font="Comic Sans MS"] translates as [/font]

[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]

[font="Comic Sans MS"]The last part[/font] [font="Courier New"]AND (ProductID != NULL)[/font] [font="Comic Sans MS"]would explain why no record would be returned since[/font]

[font="Courier New"]<anything> = NULL[/font] [font="Comic Sans MS"]always returns false.[/font]

[font="Comic Sans MS"]Assuming of course that this is indeed the way SQL Server evaluates logical expressions.

If not, then I am still at a loss as to why the [/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails.

By the way, I enjoyed the crystal-clear way to include a null in a results set without having to actually put one in the table as done by Mr. Lange.[/font]