NOT IN vs NOT EXISTS

Question

Post reply

NOT IN vs NOT EXISTS

Viewing 3 posts - 31 through 32 (of 32 total)

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 1

drew.allen - Friday, October 26, 2018 7:58 AM

Thom A - Friday, October 26, 2018 1:56 AM

jcelko212 32090 - Thursday, October 25, 2018 3:46 PM

I've heard of it, but I've never found a reason to use it given that SOME/ANY is logically equivalent to EXISTS and ALL can be expressed as NOT EXISTS.

These operators were added early in the SQL Standards to give us the logical quantifiers ï€¢and ï€¤. In fact, the IN() predicate was defined with [ANY | SOME] instead of using the Pascal language operator. I do not use them much, but sometimes the shorthand makes the code more natural to an English speaker.

Please post DDL and follow ANSI/ISO standards when asking for help.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

ChrisM@Work - Thursday, October 25, 2018 5:38 AM
Brandie Tarvin - Thursday, October 25, 2018 5:11 AM
Thom A - Thursday, October 25, 2018 4:59 AM
Perhaps my initial post mislead people, but the query in my initial post wasn't what was being "debated", when i was informed that NOT EXISTS is "the worst option", it was a totally different query in a different environment; I simply wasn't in a position to test when the discussion came up. It's something that I finally found the time to do yesterday, and was unsurprised by my findings (that in truth there is little difference for the performance of NOT IN and NOT EXISTS, but that NOT EXISTS (for me) was the fastest answer).
Thus what I was asking here was more if those (far) more educated that me were of the same belief that NOT IN > NOT EXISTS < all other methods, or if I was being fed utter nonsense and told to believe based on the "experts" self opinion (sorry, I probably wasn't clear on that). I've not had the pleasure of talking to the person since, but wanted to find out if there was any evidence to his claim. The topic here, however, strongly suggests not; which was my initial understanding.
It seems to me everyone agrees that there is no evidence for his theory.
Without evidence, a theory is all it is.

Actually, that's a hypothesis. A theory needs evidence. I'm sorry for reviving the thread but I just wanted to support what has been shown here.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

andycadley SSCertifiable Points: 5296 More actions · Answer 3

jcelko212 32090 - Friday, October 26, 2018 10:38 AM
These operators were added early in the SQL Standards to give us the logical quantifiers ï€¢and ï€¤. In fact, the IN() predicate was defined with [ANY | SOME] instead of using the Pascal language operator. I do not use them much, but sometimes the shorthand makes the code more natural to an English speaker.

When using them with = it's quite nice as it usually lets you lift the condition out of a correlated subquery, which can be clearer and also makes it easier to run the subquery manually to check it does what you mean. It gets awkward quick when you start using <> though and usually NOT IN/NOT EXISTS is more likely to do what you meant.