Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why use IN instead of EXISTS

Author  Topic 

Charles Egan
Starting Member

21 Posts

Posted - 2013-06-20 : 18:14:30
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 18:30:03
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
   

- Advertisement -