QUESTION RELATED TO SQL QUERIES

Question

I really don't understand how [NOT IN] and [IN]  clause work when there is a NULL value within the set of values.

Please have a look to the sample code attached to this mail to better understand my question.

USE TestDB

GO

CREATE DATABASE TestDB;

GO

CREATE TABLE Customer

(

custid INT NOT NULL PRIMARY KEY, 

customerName NVARCHAR(30)

)

GO 

INSERT INTO Customer

SELECT 1, 'david' union all

SELECT 2, 'tom' union all

SELECT 3, 'lisa' union all

SELECT 4, 'paker' union all

SELECT 5, 'raj' 

GO

SELECT * FROM Customer;

-- #### HERE IS MY CONCERN.

-- by using "NOT IN" in the following query I obtain the expected result

  -- customer with ID different than (1,2,3)

SELECT custid, customerName

FROM Customer

WHERE custid NOT IN (1,2,3); -- equivalent to ->  NOT(custid = 1 OR custid = 2 OR custid = 3)

GO

-- NOW WHEN I ADD A "NULL" in the set of values for the [NOT IN] CLAUSE

-- I obtain an EMPTY SET (I don't UNDERSTAND this result?)

SELECT custid, customerName

FROM Customer

WHERE custid NOT IN (null, 1,2,3); -- equivalent to ->  NOT( custid = NULL OR custid = 1 OR custid = 2 OR custid = 3)

GO

--WHEN I ADD A "NULL" in the set of values for the [IN] CLAUSE

-- I obtain THE RIGHT RESULT "Customer having ID of (1, 2 ,3)"

SELECT custid, customerName

FROM Customer

WHERE custid IN (null, 1,2,3);-- equivalent to -> ( custid = NULL OR custid = 1 OR custid = 2 OR custid = 3)

GO

I don't understand why [IN] clause seems to filter NULL values. But [NOT IN] don't filter NULL values?

Answer 1

When the comparison is done with NULL, it returns UNKNOWN because NULL is an unknown value and anything compared with unknown can only result in unknown. And UNKNOWN in this case of Logical comparison is treated as FALSE and an empty result set is being returned.

Alternate solution for this is to put an additional filter:

SELECT    custid, customerName    
FROM    Customer
WHERE    (custid NOT IN (1,2,3) OR custid IS NULL);