Should left outer joins multiple tables or not? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Should left outer joins multiple tables or not?

I’m interested to know whether there is any general performance tips about using left outer join to join multiple tables in a select statement besides having indexes for the joining columns.
Thank you for any help.
1. Avoid them if you can use an INNER JOIN. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />2. This isn’t supposed to matter, but have your most limiting at the top of the FROM. It does matter.<br /><br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Hi derrickleggett, Can you elaborate on your suggestion #2?
Thank you.
When you use the left outer join, you will have basically a cascading list of joins. A lot of times, people use the left outer joins, then somewhere down the list say where right side IS NOT NULL. This will in effect limit the entire recordset on that table. Instead of joining that way, list that table closer to the top or as the top table. Use a combination of INNER JOIN; or have the table as your source table. This will cause the "filtering" to occur sooner in the process and the rest of the process will deal with a smaller recordset. Does that make sense? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
http://www.sql-server-performance.com/tuning_joins.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Originally posted by pcsql I’m interested to know whether there is any general performance tips about using left outer join to join multiple tables in a select statement besides having indexes for the joining columns.
Thank you for any help.

It might be a good investment for you to spent some $60 for Inside SQL Server by Kalen Delaney. Covers locking, joining strategies and a lot more.
–Frank
http://www.insidesql.de

derrickleggett, For my case, I have tried without any where condition so all the tables are joined by its respective keys. Thank you for the clarification. ——————-
Satya, Thank you for the link. ——————-
Frank, I have that book but not finish reading it (actually only touch little bit of it).
For this type of thing, I actually like Ken Henderson’s books better. Look up The Guru’s Guide to Transact-SQL. Kalen is really good on the architecture and DBA work. I like the Guru’s books better for proc, joins, etc. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I thought the question was about JOINs, strategies and general performance, not general T-SQL so I mentioned Inside SQL Server as it surely helps choosing the right joining strategy when you know what’s going on behind the scenes. Although I also have the Henderson book, meanwhile I’m uneasy about it. I like the Ben-Gan, Moreau book more. But I also guess it depends on personal preferences.
–Frank
http://www.insidesql.de

Whynot have both the books for reference. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
]]>