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.
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: slowerEXISTS• 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 clauseCREATE 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; |
|
|
|
|
|