SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why use IN instead of EXISTS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Charles Egan
Starting Member

21 Posts

Posted - 06/20/2013 :  18:14:30  Show Profile  Reply with Quote
I’m trying to really understand the difference between IN and EXISTS. Would the following be fair statements?

IN
• first evaluates: inner query
• evaluates outer query until: every row in outer query's table examined
• accepts as argument: a list of literal values or a condition (most often used when argument is a list of literal values)
• is faster when inner query's table contains: few records / values
• is usually: slower

EXISTS
• first evaluates: outer query
• evaluates outer query until: inner query finds a record that satifies condition
(if inner query doesn't find any records that satify condition: until every row in outer query's table examined)
• accepts as argument: a condition
• is faster when inner query's table contains: many records
• is usually: faster

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 06/20/2013 :  18:30:03  Show Profile  Reply with Quote
It used to be that EXISTS (or NOT EXISTS) is faster. But, with SQL 2005 and later (I may be wrong in the version), the query plan generated for both IN and EXISTS are identical in MOST cases.

However, there ARE logical differences. For example, see the example below. Also, there are cases where logically something that can be done with a single EXISTS or NOT EXISTS would require two separate conditions when using the IN clause
CREATE TABLE #A(id INT);
INSERT INTO #A VALUES (1),(2);

CREATE TABLE #B(id INT);
INSERT INTO #B VALUES (1),(NULL);

SELECT * FROM #A WHERE id NOT IN (SELECT id FROM #B);
SELECT * FROM #A a WHERE NOT EXISTS (SELECT * FROM #B b WHERE a.id = b.id);


DROP TABLE #A;
DROP TABLE #B;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000