March 15, 2015 at 6:35 am
So, one thing I've been having issues with is differentiating between IN and EXISTS, as essentially they seem to do the same thing in my eyes but I know there is a difference and I've experienced a difference but I'm having a problem connecting the dots as to why. Can anyone clear this up to me or point me to a good resource to help clear this up? Thanks!
March 15, 2015 at 1:10 pm
This is a very good article on this subject:
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]
-- Itzik Ben-Gan 2001
March 16, 2015 at 5:21 am
A non-technical way of looking at this...
If you are in a meeting room and want to know if anyone has a red shirt, you can do one of two things:
a) look round until you see the first red shirt, then stop looking
b) look at everyone and write down what colour shirt they are wearing. After you have done this for everyone then check your list to see if anyone has a red shirt.
If the meeting room is small with only two or three people both ways will take about the same amount of time. If the meeting room has 100 people then using a list will take longer. Your list may show that maybe 20 people are wearing a red shirt, but this is not useful information because you only wanted to know if at least one person had a red shirt.
a) is obviously the EXIST check and b) is the IN check. When you move back to the database world then all the technical things that Alan linked to also become important.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 16, 2015 at 5:41 am
EdVassie (3/16/2015)
a) is obviously the EXIST check and b) is the IN check.
Except it isn't. They behave and perform identically in most cases, see the blog post referenced above.
b) would be the real world equivalent of
SELECT @ShirtCount = COUNT(*) from PeopleInRoom WHERE ShirtColour = 'Red'
IF @ShirtCount > 0
PRINT 'There is a red shirt'
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply