CODEWITHSUNDEEP
sqlsql-servert-sql
shole
shole

Reputation: 4094

Find deleted rows: Not EXISTS vs Not IN

In my case, I have two table with same structure: TableA & TableB, and what I was trying to do is to find if there is any records only exists in A but not B.

My script was 

SELECT * FROM TableA
WHERE NOT EXISTS (
    SELECT * FROM TableB
)

While there is 2 records which only exists in A but not B, this script returns nothing. Then I changed into following:

SELECT ID FROM TableA
WHERE ID NOT IN (
    SELECT ID FROM TableB
)

This script works successfully and return the 2 records' ID.

My question is: Is this behavior normal? What is the mechanism behind NOT EXISTS and NOT IN?

I have read some other posts comparing NOT EXISTS and NOT IN, and most people suggest using NOT EXISTS in 99.9% scenarios, is this case fall into that 0.1% which NOT EXISTS is not applicable? (I believed it's due to my wrongly usage though, please correct me if that's the case)

Upvotes: 0

Views: 57

Answers (2)

cha
cha

Reputation: 10411

You need to be careful with the NOT IN expression. The A NOT IN(B,C,D) expression basically means (A<>B AND A<>C AND A<>D). If any of the values are NULL the whole expression will become NULL.

So, applicable to your example the correct NOT IN expression should be (unless the ID is not nullable column):

SELECT ID FROM TableA
WHERE ID NOT IN (
    SELECT ID FROM TableB WHERE ID IS NOT NULL
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you want to look at all the values in the rows, then use EXCEPT:

SELECT *
FROM TableA
EXCEPT
SELECT *
FROM TableB;

If you want to use NOT EXISTS correctly, then you need a correlation clause:

SELECT a.*
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.id = a.id);

I strongly recommend using NOT EXISTS over NOT IN with a subquery. NOT IN will return no rows at all if b.id is ever NULL. That is usually not what is intended. NOT EXISTS matches the expected semantics.

Upvotes: 3

Related Questions