Execution Plan Question

[pwilson]

Looking at the estimated execution plan for these they look the same, i think. Are there any differences?

Select * from Table1 T1 INNER JOIN Table2 T2 ON T1.ID=T2.T1ID WHERE T1.Field1=@var

Select * from (SELECT * From Table1 WHERE Field1=@var) AS T1 INNER JOIN Table2 T2 ON T1.ID=T2.T1ID

And would anything be different if it were an right/left/full join?

I just want to make sure I'm joining things in the best poosible way.

 

[jbenson001]

I think the sub select in the second query could cause performance issues with large data sets.

 

[nomadicalloy]

left outer join is better, depending on the indexing and relationship.

SQL Help Blog

http://www.xpherion.com/blog.asp

 

[vongrunt]

Some six months ago we did relatively detailed testing about joins vs IN/EXISTS vs derived tables. Most of the time differences were negligible (<10%). IN/ANY/EXISTS were overall worse on larger data sets.

IMHO join type should follow foreign key column definition - INNER JOIN for NOT NULL, LEFT OUTER JOIN for NULL.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)