NOT IN vs. NOT EXISTS

Question

NOT IN vs. NOT EXISTS

Ben

Old Hand

Points: 338
More actions
August 2, 2021 at 4:49 pm

#3916228
This is a basic question which occurred to me while reading something on the usage of NOT IN and NOT EXISTS. It made me to go back and look into a query I wrote a few years go to compare differences.
In below queries, although they both give me the same result and same processing time, I was wondering which is better (less costly) to use in such cases? Comparing a value to a list (NOT IN) or a Boolean (NOT EXISTS)?
```
SELECT 
a.AcNo, a.CustID, a.ClientName
FROM table_1 a 
WHERE a.custID NOT IN (SELECT b.CustID FROM table_2 b WHERE b.CustID=a.CustID)


SELECT 
a.AcNo, a.CustID, a.ClientName
FROM table_1 a 
WHERE NOT EXISTS (SELECT b.CustID FROM table_2 b WHERE b.CustID=a.CustID)
```

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 1

Hi,

If memory servers me in the case of EXISTS vs IN, then EXISTS works slightly better as the first occurrence of table_1 record in table_2 and it will move onto the next record to check, in IN it continues to check the whole table, not sure if it behaves the same with NOT IN and NOT EXISTS as to me it would still have to check the whole table. I've never observed a meaningful time/resource difference though.

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

ScottPletcher SSC Guru Points: 101074 More actions · Answer 2

Use NOT EXISTS when possible, as it allows for better execution plans.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 3

Not exists is much atomic than sets.

Use Not Exists.

Cheers,
John Esraelo

Jeffrey Williams SSC Guru Points: 90301 More actions · Answer 4

You may want to change your NOT IN to exclude the correlation. It isn't needed and may be skewing the results...the real answer to your question is: it depends.

EXISTS vs IN - the general belief is that EXISTS performs better. However, that all depends on how SQL Server generates the execution plan. NOT EXISTS vs NOT IN isn't as clear.

However, that is not the best reason for using NOT EXISTS vs using NOT IN - the best reason to avoid NOT IN is the unexpected results of including a NULL value in the set. If the query for NOT IN returns a NULL value then no results will be returned - where using NOT EXISTS avoids that problem.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs