Sub Query NULL Got ya

Using a negative (NOT IN) Sub Query that contains a NULL value returns no result.

Use Northwind

--(1) works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                IN (1,10)

--(2) incorporates and NULL value, works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                IN (NULL,1,10)

--(3) negative, works as normal
SELECT *        FROM Products 
                WHERE ProductID 
                NOT IN (1,10)

--(4) negative with NULL, doesn't return values
SELECT *        FROM Products 
                WHERE ProductID 
                NOT IN (NULL,1,10)




--related best practise, INDEX HINT
SELECT *        FROM Products 
                (INDEX = PK_Products) 
                WHERE ProductID 
                NOT IN (1,10)

--related best practice, RANGE
SELECT *        FROM Products 
                WHERE ProductID 
                BETWEEN 1 AND 10