Can I use NULL with the IN operator?

Can I use NULL with the IN operator?

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 9/8/2016
-- the IN operator and NULL
-- aka Can I use IN with NULL?



-- the short answer is no
-- but why?

-- a couple of simple examples

SELECT 1
WHERE  NULL IN (1, 2, 3)

-- notice that NULL is not IN a list that contains a NULL

SELECT 1
WHERE  NULL IN (NULL, 1, 2, 3)

-- ok, let's look at a table I have

USE ReferenceDB


-- this is very close to what I saw in a piece of code
SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')


-- so are there zero-length strings? yes
SELECT  lastname,
        email
FROM    Person
WHERE   email = ''

-- are there NULLs? no?
SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL

-- ah, for test for a NULL value, you need the IS operator
SELECT  lastname,
        email
FROM    Person
WHERE   email IS NULL

-- and more proof...
SELECT 1
WHERE  NULL = NULL

SELECT 1
WHERE  NULL IS NULL

-- but is an IN *really* just a translation of a sequence of OR'ed equalities?

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')

SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL
   OR   email = ''

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '', 'fred')

-- so, the query optimizer is smart enough to
-- remove the NULL from the IN list, since it doesn't matter