kesteloot
asked on
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.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
not necessarily. it might be possible, depending on the statistis/cardinality on the joined condition
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
In other words, is the INNER JOIN inherently more efficient than an OUTER JOIN?
Thanks again for taking time to answer.
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.