Using EXISTS - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Using EXISTS

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-23 : 04:29:30

Hi,

I pointed out to a fellow developer of mine a few days back that I though his use of EXISTS was really pointless and I asked why e wasn't using joins instead. He told me that EXISTS was faster but I didn't belive him so a made a simple test:

DECLARE @table1 TABLE (ID1 INT PRIMARY KEY CLUSTERED, Value1 varchar(200))
DECLARE @table2 TABLE (ID2 INT PRIMARY KEY CLUSTERED, Value2 varchar(200))

SELECT a.* 
FROM @table1 a 
  INNER JOIN @table2 b
    ON a.ID1 = b.ID2
WHERE Value2 > 10

SELECT * 
FROM @table1 a 
WHERE EXISTS (
  SELECT ID2 FROM @table2 b WHERE ID2 = a.ID1 AND Value2 > 10
)

These two queries however give the exact same execution plan, so our argument is now at a standstill. Will either of the two give any performance gains over the other in difefrent scenarios?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-23 : 04:36:01

there was a discussion about this and we came to a conclusion that exists was faster.
however the difference was noticable only at very large tables.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70056

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 05:12:11

How about putting the second comparison to the JOIN clause?

SELECT		a.* 
FROM		@table1 a 
INNER JOIN	@table2 b ON b.ID2 = a.ID1 AND b.Value2 > 10

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-23 : 06:15:11

I've seen your solution before too Peso but I just regarded it as the same as putting it in a where-clause only more confusing. I spent a few hours once debugging a large query and found that the guy who made it put a comparison in the join like you did. Pi$$ed me off to be honest hehe...but will it be interpreted differently by the query optimizer?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-23 : 09:27:48

as long as it's inner join it's interpreted the same.

using left join i've seen the differnce between execution plans for left join and exists.
th eleft join had one operation more. it was filtering if i remember correctly.
the table had identity PK CI and 1 millon+ rows

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources