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:
I checked that NOT IN fails (no record returned because of the extra null) as opposed to IN which does work.
WHERE ProductID IN (1, 2, 3, 4, NULL) translates as
WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL
WHERE ProductID NOT IN (1, 2, 3, 4, NULL) translates as
WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)
Using Boolean algebra the last expression should yield the same results
WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)
The last part AND (ProductID != NULL) would explain why no record would be returned since
<anything> = NULL always returns false.
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 NOT IN 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.