SQL Server Performance Forum – Threads Archive

Join Performance

I have a huge query where I must use several JOINs between tables.
The application requires the best performance and efficiency possible, so I would like to know the difference in performance and efficiency between the different "JOIN possibilities":
– JOIN
– OUTER JOIN
– LEFT OUTER JOIN
– INNER JOIN
– By hand (do the JOIN on the WHERE clause: A.field = B.field)
– Etc Thanx a lot!

I would say, that this isn’t a question of performance, but rather what your query logic requires. The simple JOIN word in SQL Server is equivalent to (INNER) JOIN. (OUTER) JOIN is also not needed. LEFT (or RIGHT) JOIN is sufficient. Doing the INNER JOIN explicit in the JOIN clause or in the WHERE clause is detected by the optimizer and internally translated to the JOIN clause. I think, it is better if you ask for a specific problem. Such a broad question can hardly be answered satisfyingly. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Anyway, that’s enough for me.
I only want to know the differences in performance between those JOINs.
But, according to what you said, INNER JOIN, JOIN and JOIN (in WHERE clause) are the same.
The obvious difference is between INNER JOIN and OUTER JOIN, but has different implications…so it’s not important because I don’t need to use OUTER JOIN. So your information is very usefull.
Thanx!!

Give the SQL Optimizer for Visual Studio trial a spin. It will rewrite your SQL Statement in every possible way and determine which is the best way to write the SQL under your environment. You can download it at the following URL
http://www.extensibles.com/modules.php?name=Products&op=SSP The Relentless One
No Bug says alive http://www.extensibles.com

]]>