Is there a performance implication with an OUTER JOIN versus an INNER JOIN

I was wondering if a "LEFT OUTER JOIN" is less efficient than an INNER JOIN.  I'm considering adding a dummy row to make an INNER JOIN possible (yes, I know its ugly), but want to make sure it'll be worth the effort.
kestelootAsked:
Who is Participating?
 
ewahnerCommented:
You are gonna need to get a little more detailed, because I am having a hard time making sense out of this.

You don't have to have a relationship between the two tables other than like values.  So OrderId in one table and an OrderId in another table...no foreign key relations but obviously the data would be related, so of course you can inner join them.  Now if you are saying don't omit records in table "A" just because they don't have a corresponding record in table "B" then sure do a left outer join.

To take it a step further you can pull only "A" records that don't have a matching "B" record by testing for null on B.KeyID
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not necessarily. it might be possible, depending on the statistis/cardinality on the joined condition
0
 
Atlanta_MikeCommented:
It would be counterproductive to add a dummy row. A LEFT JOIN is not necessarily less efficient, the deciding factor is in what conditions are being set and the keys being used.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kestelootAuthor Commented:
I'm sorry, I thought the question was more black and white.

Let's say the primary table (A) has 4 times the records of table (B).

The key of table B is "KeyID".

I'd like to join table A to table B with:
    FROM A INNER JOIN B ON (A.ForeignKeyID = B.KeyID)
but I can't because there is not a required relationship between the two, as such I must join with:
    FROM A LEFT OUTER JOIN B ON (A.ForeignKeyID = B.KeyID)

As a hack, I could require the relationship and use 0 as the ForeignKeyID in table A rather than NULL.  I would then create a "dummy" record in B with a KeyID of 0.

Is the hack worth it from a performance standpoint?  Please understand that execution plans are like a foreign language to me.

Thank you for any help you can offer.
0
 
kestelootAuthor Commented:
The whole motivation behind my question is performance.  I want to pull all the records from Table A, therefore the OUTER JOIN.  Would it be worth it to have a dummy record in Table B such that I can perform an INNER JOIN, purely from a performance perspective?

In other words, is the INNER JOIN inherently more efficient than an OUTER JOIN?

Thanks again for taking time to answer.
0
 
ewahnerCommented:
No it is not more efficient in that circumstance.  You would be violating the reason for doing an INNER JOIN by adding dummy records.  An INNER join is used to provide INTERSECTION between two groups of data, whereas an OUTER JOIN is for UNION.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.