CODEWITHSUNDEEP
sql-server
Ben
Ben

Reputation: 609

Strange results from NOT IN subquery

I have this simple query:

SELECT COUNT(SalesOrderId)
FROM SalesOrder

It gives a result of 14000

Then I have

SELECT COUNT(SalesOrderId)
FROM SalesOrder
WHERE SalesOrderId     IN (SELECT New_DSheetId FROM New_dealsheet)

That gives 4169

If I then do

SELECT COUNT(SalesOrderId)
FROM SalesOrder
WHERE SalesOrderId NOT IN (SELECT New_DSheetId FROM New_dealsheet)

I'm expecting 9831 but the result is 0

Not sure why that is?

Upvotes: 1

Views: 46

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

NOT IN with NULL could be tricky.

Let's unwrap it:

col IN (1,2,3, NULL)
<=>
(col = 1) OR (col = 2) OR (col = 3) OR (col = NULL)

For now everything is ok. But it isn't. We cannot compare directly value to NULL because the result is unknown.

Let's check negated condition:

col NOT IN (1,2,3,NULL)
<=>
(col != 1) AND (col != 2) AND (col != 3) AND (col != NULL) 
                                              -- here is the problem

LiveDemo

To sum up last condition is always not true. That is why you get 0 records.

When you use NOT IN make sure that you do not compare with NULL value.

Upvotes: 2

Devart
Devart

Reputation: 121922

SELECT COUNT_BIG(s.SalesOrderId)
FROM dbo.SalesOrder s
WHERE s.SalesOrderId NOT IN
    (
        SELECT n.New_DSheetId
        FROM dbo.New_dealsheet n
        WHERE n.New_DSheetId IS NOT NULL
    )

Upvotes: 1

Related Questions