Are LEFT JOINs bad? (slow, inefficient?)  How else query optional foreign records?

ZuZuPetals
ZuZuPetals used Ask the Experts™
on
Background: If I have a main table and many other foreign tables containing optional/sparse related information, one must do a LEFT JOIN to select the main and option data.

I've heard/read that LEFT JOINs are less efficient/slower than INNER JOINs.   Googling isn't turning up much info.  Is this true?

If they are slower, is there an alternative to using LEFT JOINs in the scenario above? (main table + many optional foreign tables containing sparse info).

Extreme case: If I have a main table and, say, at an extreme, fifteen (15) other related tables?   Wouldn't doing 15 LEFT JOINs bring SQL Server 2005 to it's knees?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
no.. if the case returning the whole main table along withe the related information on the detail/foreign tables then the left joins would be the best case.
Wouldn't doing 15 LEFT JOINs bring SQL Server 2005 to it's knees? No... sql server not such a week tool.

Have you considered using subqueries for the data fields from those tables?
 
I don't know the performance issues one way or the other though.
 
Kelvin
Awarded 2008
Awarded 2008

Commented:
Yes, outer joins are slower than inner joins. Performance depends on the amount of data you have...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior Database Architect
Commented:
Yes, LOJ are probably less efficient than an Inner Join as the optimizer has fewer options to work with, but they are usually more efficient than any work-arround like lots of subqueries.  They are an intrigral part of normalized databases and should work fine if your database is designed properly.
You want to make sure that they are using FK look ups to the Primary Key or an alternate unique key of the table you are joining to.  If not then you may want to see if additional indexes would improve performance.
I can't say we have any queries going to 15 FK tables at once but I know we use multiple LOJs all over the place.
jorge_torizResearch & Development Manager
Commented:
In the SQL Server books say that several joins are better if you use some denormalization, search about query optimization, you will find this.

Why don't use indexed views.
Awarded 2008
Awarded 2008
Commented:
>>In the SQL Server books say that several joins are better if you use some denormalization, search about query optimization, you will find this.

That doesn't make any sense...you denormalize so that you can remove the use of joins.

Outer joins are less effecient than an inner (equal) join because of the comparison involved.  An inner join searches for where two records are equal to each other...effecient use of indexes if they are present.  Outer joins, on the other hand is more like an OR statement rather than an AND statement.  You find where one value is equal to another value OR that other value is NULL....ineffecient use of indexes.  You can sometimes get around this existence check using NOT EXISTS(), but the performance isn't much better.

In terms of 15 joins...its going to be harder for the optimizer to decide a good plan through joining all of those tables...SQL (or any db engine) can "handle" it, but not well.

HTH,
Tim
jorge_torizResearch & Development Manager

Commented:
If you know about database design... you could see that denormalization can avoid some joins in a query.

There isn't a good plan to join 15 tables if we take in mind the order the query is executed, remember that joins imply crossing the tables and then apply the ON operator.
Top Expert 2012

Commented:
>>If you know about database design... <<
That was un-called for.  I suggest you double check your facts and who you are talking to.
jorge_torizResearch & Development Manager

Commented:
Learning to read is a good suggestion for you acperkins... nobody is talking to you, even nobody take care of your "suggestions", that message was for chap.
Awarded 2008
Awarded 2008

Commented:
>>If you know about database design... you could see that denormalization can avoid some joins in a query.

This message was for me?  Did you not see my comment "That doesn't make any sense...you denormalize so that you can remove the use of joins."
Top Expert 2012

Commented:
>>nobody is talking to you, even nobody take care of your "suggestions", that message was for chap.<<
I am not sure if you are aware, but you are posting in a public site.  Let me remind you that this is a community and as such we abide by the EE guidelines.  Here are the relevant parts:
Be professional: Treat the asker and your colleagues as professionals. Check your ego and your attitude at the door; rudeness, derogatory comments, and sarcastic remarks are uncalled for and unnecessary.
Avoid criticizing: There's nothing to be gained by criticizing another Member when disagreeing with his/her suggestions. Don't take a critical comment personally; stay focused on the object -- solving the asker's problem.
Top Expert 2012

Commented:
Jorge,

In case you did not know Tim (chapmandew) has written many technical articles on MS SQL Server, so he does not need to be lectured on databases.

Look, I realize English may not be your first language, so if this is not clear, let me know and I will repeat everything I just said in (chilango) Spanish.

Anthony

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial