Not exists or Not In which one is better

Question

Not exists or Not In which one is better

komal145

SSCrazy Eights

Points: 9918
More actions
July 9, 2012 at 1:30 pm

#258579

Today I had a question :
Not exists or Not In which one is better for perfomance??? Or any other you can use than this for perfomance???
acn anyone plesae answer to this one??

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Not Exists is usually faster, and has the advantage that you can use multiple columns in it for the validation. Not In is usually better for hard-coded sets of values. For example, "Where Country Not In ('USA','CAN','DEU')" will usually be plenty fast, and would be much harder to code in a Not Exists format.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Unless you're dealing with nulls, no difference. If you are dealing with nulls, the behaviour is different so you need to check which one gives the correct results

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

I would say if not exists

***The first step is always the hardest *******

ScottPletcher SSC Guru Points: 101118 More actions · Answer 4

In general, I too prefer NOT EXISTS, although SQL will often generate identical plans for either one.

It's definitely strongly preferred whenever possible to have an index to support the lookup, for either NOT IN or NOT EXISTS.

For hard-coded values, insert them into a table with a clustered index yourself.

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".