SQL Server performance - Subselect or Inner Join?

Question

I've been pondering the question which of those 2 Statements might have a higher performance (and why):

select * from formelement 
where formid = (select id from form where name = 'Test')

or

select * 
from formelement fe 
inner join form f on fe.formid = f.id 
where f.name = 'Test'

One form contains several form elements, one form element is always part of one form.

Thanks,

Dennis

Nice Idea, to be honest I had no Idea this feature existed. But while this answers the question what exactly needs performance, it doesn't really inform me about why that is. — Dennis Röttger
– Dennis Röttger, Commented Apr 29, 2011 at 11:56
I removed my answer :) For the execution plans, they WILL tell you why. You will see if the operations are different, and the type of operations executed will give you the reasoning. — JNK
– JNK, Commented Apr 29, 2011 at 12:01

SQLMenace · Accepted Answer · 2011-04-29 11:53:06Z

3

look at the execution plan, most likely it will be the same if you add the filtering to the join, that said the join will return everything from both tables, the in will not

I actually prefer EXISTS over those two

select * from formelement  fe
where exists (select 1 from form f 
                 where f.name='Test' 
                 and fe.formid =f.id)

answered Apr 29, 2011 at 11:53

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JNK Over a year ago

IN would be equivalent to exists for this as well, correct (exec plan wise)?

SQLMenace Over a year ago

Yes, should be the same, I like exists more because you don't have to worry about NULLS or if you mistype the column name which won't throw an error if the column name exists in the formelement table. Example here: forum.lessthandot.com/viewtopic.php?f=17&t=14123

Heinzi · Accepted Answer · 2011-04-29 11:58:56Z

1

The performance depends on the query plan choosen by the SQL Server Engine. The query plan depends on a lot of factors, including (but not limited to) the SQL, the exact table structure, the statistics of the tables, available indexes, etc.

Since your two queries are quite simple, my guess would be that they result in the same (or a very similar) execution plan, thus yielding comparable performance.

(For large, complicated queries, the exact wording of the SQL can make a difference, the book SQL Tuning by Dan Tow gives a lot of great advice on that.)

answered Apr 29, 2011 at 11:58

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Collectives™ on Stack Overflow

SQL Server performance - Subselect or Inner Join?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related