+1 vote
1 view
in Devops and Agile by (28.1k points)

I have a concern about how the clause NOT IN works when it is applied to a list that contains NULL values; The result is an empty table with NO RECORDS. please do open the SQL file attached with this email to better understand, I commented the code for it to be easy to understand.

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

CREATE TABLE CustomerWhoBuy(

custid INT  NULL,

customerName NVARCHAR(40)

)

GO 

INSERT INTO CustomerWhoBuy

SELECT 2, 'tom' union all

SELECT 3, 'lisa' union all

SELECT 4, 'paker'

go

-- #### HERE IS MY CONCERN.

-- I want to extract customers from "Customer" table who are NOT IN "CustomerWhoBuy" table 

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

SELECT custid, customerName

FROM Customer

WHERE custid NOT IN (SELECT O.custid

    FROM CustomerWhoBuy AS O);

GO

--#### Now this is where it gets weird 

-- I insert a new customer in the table "CustomerWhoBuy" with custid = NULL

INSERT INTO CustomerWhoBuy

SELECT NULL, 'bill'

GO

/*

 AND NOW THE SAME QUERY THAT I USED TO extract customers from "Customer" 

 table who are NOT IN "CustomerWhoBuy" table  gives me a RESULT WITH NO RECORD

*/

-- QUESTION 1 : WHY DO I OBTAIN A RESULT WITH NO RECORD WHEN USING "NOT IN" ON A LIST THAT CONTAINS NULL VALUES?

SELECT custid, customerName

FROM Customer

WHERE custid NOT IN (SELECT O.custid

    FROM CustomerWhoBuy AS O);

GO

--## THIS IS HOW I USED "NOT EXIST" TO SOLVE THE PROBLEM.

SELECT custid, customerName

FROM Customer C

WHERE not exists (SELECT O.custid

    FROM CustomerWhoBuy AS O

WHERE C.custid = O.custid);

GO

-- QUESTION 2: IS IT BETTER TO USE "NOT EXISTS" instead of "NOT IN"? Is there a rules of thumb that help

--    to choose between those 2 clauses?

1 Answer

0 votes
by (48k points)
you declared the 'custID' as primary key with NOT NULL but when you need to get the details from the 'customers table' and who are not in the 'customersWhoBuy',
which means you need to have the details of 'CustomersWhoBuy' table, but you gave 'custID' as NULL for 'customersWhoBuy' table. You have used 'NOT IN' command with the 'CustID', Which has NULL values so that the reason you are getting 'NoRecords'.
To resolve this you need to change that 'custID' from 'NULL'. Hope this will help you in resolving this issue.

Related questions

+1 vote
1 answer
asked Oct 14, 2019 in SQL by chandra (28.1k points)
0 votes
0 answers
0 votes
1 answer
+2 votes
1 answer
0 votes
1 answer
asked Mar 2 in SQL by Sudhir_1997 (37.5k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...