[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-05-11
Medium Priority
Last Modified: 2010-08-05
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.
Question by:kesteloot
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16660696
not necessarily. it might be possible, depending on the statistis/cardinality on the joined condition
LVL 13

Expert Comment

ID: 16660902
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.

Author Comment

ID: 16661858
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:

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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

ewahner earned 1000 total points
ID: 16664690
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

Author Comment

ID: 16668860
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.

Expert Comment

ID: 16683333
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question