New OOTS products from CafePress
New OOTS t-shirts, ornaments, mugs, bags, and more
Results 1 to 9 of 9
  1. - Top - End - #1
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default SQL: RIGHT or LEFT JOIN more efficient

    From my understanding, a RIGHT or LEFT JOIN is functionally the same in the sense that data1 RIGHT JOIN data2 is the meaning as data2 LEFT JOIN data1. That makes me think they would be equally efficient, because the SQL engine would be doing the same thing 'behind the scenes'.
    But in a recent programming job, something made me wonder. (the 'something' was I used up the entire server's memory, but I think it was more the ORDER BY than any JOIN)

    I know there's a lot of 'dialects' of SQL out there, and realize the answer may change based on the actual SQL compiler (?) being used. I'm using the PROC SQL utility in SAS to do SQL coding right now, but this is also a general question.

    Is RIGHT or LEFT sometimes more efficient?
    I could see it being the case when you only expect a small number of matches via the JOIN. E.g., maybe LEFT is more efficient if the data on the right side will have only a few matches via the ON variables.


    edit: FYI that I learned the server was clogged with a ton of orphaned data, so it wasn't just my task being overly large.
    Last edited by JeenLeen; 2021-03-08 at 11:21 AM.

  2. - Top - End - #2
    Colossus in the Playground
     
    BlackDragon

    Join Date
    Feb 2007
    Location
    Manchester, UK
    Gender
    Male

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    There's absolutely no reason why either should be any more efficient, because you can actually write the same statement using either--you just have to swap your table names around. There are far more sources of inefficiency in a SQL statement, especially when applied to a large data set.

  3. - Top - End - #3
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    Quote Originally Posted by factotum View Post
    There's absolutely no reason why either should be any more efficient, because you can actually write the same statement using either--you just have to swap your table names around. There are far more sources of inefficiency in a SQL statement, especially when applied to a large data set.
    Thanks.
    I figured the issue was the "second pass through the data" to do a DISTINCT or the sorting via ORDER BY. Well, actually the log note said it was running out of memory while doing the sorting, so I'm pretty sure it's the ORDER BY. But I felt like asking.

    I generally only do LEFT JOINs out of habit, but it seemed more intuitive to do RIGHT JOINs for this particular project, and just got me wondering.

  4. - Top - End - #4
    Troll in the Playground
     
    Flumph

    Join Date
    Nov 2006
    Location
    England. Ish.
    Gender
    Male

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    In any case, the query optimizer (provided your rdbms has on) should decide which way to drive the join, based on table statistics and other information in the query.

    I remember in one large query the optimizer swapped round the last two tables in the query because it worked out that it needed to read all the rows in the data range on the last table and only half the rows in the other, so swapping them round cut the number of reads required in half. I was quite impressed - it was obvious in hindsight when looking at the data structures, but nobody had noticed...
    Warning: This posting may contain wit, wisdom, pathos, irony, satire, sarcasm and puns. And traces of nut.

    "The main skill of a good ruler seems to be not preventing the conflagrations but rather keeping them contained enough they rate more as campfires." Rogar Demonblud

    "Hold on just a d*** second. UK has spam callers that try to get you to buy conservatories?!? Even y'alls spammers are higher class than ours!" Peelee

  5. - Top - End - #5
    Troll in the Playground
     
    Imp

    Join Date
    Jul 2008
    Location
    Sweden
    Gender
    Male

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    Outer left and outer right are exactly the same. Inner join is slightly faster because it does slightly less work.
    The top answer on stack overflow goes into more detail.

    When you're dealing with legacy data and data structures that has gone through several generations it gets too complicated for me. I can offer no advice
    Last edited by Mastikator; 2021-03-08 at 01:05 PM.
    Black text is for sarcasm, also sincerity. You'll just have to read between the lines and infer from context like an animal

  6. - Top - End - #6
    Titan in the Playground
     
    Planetar

    Join Date
    Dec 2006
    Location
    Raleigh NC
    Gender
    Male

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    Stack Overflow


    This bears up with my own experience: Inner join is more efficient than either right or left join, but right or left join are, in the abstract, interchangeable. Which one is more efficient depends entirely on the tables you are joining. However, I notice that programmers seem to prefer left join as a matter of convention; I have seen many, many left joins in my time but hardly ever a right join outside a textbook.

    Respectfully,

    Brian P.
    "Every lie we tell incurs a debt to the truth. Sooner or later, that debt is paid."

    -Valery Legasov in Chernobyl

  7. - Top - End - #7
    Titan in the Playground
     
    Jasdoif's Avatar

    Join Date
    Mar 2007
    Location
    Oregon, USA

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    Quote Originally Posted by pendell View Post
    However, I notice that programmers seem to prefer left join as a matter of convention; I have seen many, many left joins in my time but hardly ever a right join outside a textbook.
    Yes. Generally the most important table is mentioned first; so in a case where you want everything from one of the tables in a join, typically that table will be on the left.
    Feytouched Banana eldritch disciple avatar by...me!

    The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas

  8. - Top - End - #8
    Troll in the Playground
     
    PaladinGuy

    Join Date
    Mar 2012
    Location
    UK
    Gender
    Male

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    It is probably also worth noting that SQL engines don't always get their automatic query optimisation correct. Things are going to be far better now than when I actually knew some of the query execution/optimisation commands (20+ years ago) but for really complex queries and joins it can be that the nature of the data can make one way of executing significantly faster than another (e.g. do you hit the 100 record no-key join before the 100 million record primary key join?).
    Knowing the ins and outs of optimising query execution used to be (and may well still be) a very lucrative profession and this is where knowing how the SQL engine in use handles left and right joins may make a difference, even though they are theoretically identical in the SQL language definition.

  9. - Top - End - #9
    Ettin in the Playground
     
    Chimera

    Join Date
    Dec 2015

    Default Re: SQL: RIGHT or LEFT JOIN more efficient

    Quote Originally Posted by JeenLeen View Post
    Thanks.
    I figured the issue was the "second pass through the data" to do a DISTINCT or the sorting via ORDER BY. Well, actually the log note said it was running out of memory while doing the sorting, so I'm pretty sure it's the ORDER BY. But I felt like asking.

    I generally only do LEFT JOINs out of habit, but it seemed more intuitive to do RIGHT JOINs for this particular project, and just got me wondering.
    I recognize that your OP question was theory rather than application, but as general advise, I always remind my programmers to do each step in isolation if you can help it. Sorts in one step, minimize each table about to be joined to the smallest size you can manage (making a sub-table with reduced variables for one; reducing down by filter criteria; and doing distincts), then doing joins. I can't count the number of times a programmer with 25+ years of experience who can run circles around me (a managers with hopefully competent but by no means exhaustive programming experience) has said that there's 'nothing wrong' with their code and it couldn't possibly be the issue and then I find that the code is trying to join 2-3 multi-million rowed, 300-variable dimension tables while doing a distinct and a filter and multiple complex case-whens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •