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

Posted on 2006-05-11
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 142

    Expert Comment

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

    Expert Comment

    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

    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.
    LVL 2

    Accepted Solution

    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 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

    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.
    LVL 2

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now