+1 vote
1 view
in SQL by (25.5k points)
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?

1 Answer

0 votes
by (42.7k points)
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);

Related questions

+1 vote
1 answer
0 votes
0 answers
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...