Results 1 to 9 of 9
-
2021-03-08, 10:54 AM (ISO 8601)
- Join Date
- Jan 2009
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.
-
2021-03-08, 11:36 AM (ISO 8601)
- Join Date
- Feb 2007
- Location
- Manchester, UK
- Gender
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.
-
2021-03-08, 11:44 AM (ISO 8601)
- Join Date
- Jan 2009
Re: SQL: RIGHT or LEFT JOIN more efficient
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.
-
2021-03-08, 12:10 PM (ISO 8601)
- Join Date
- Nov 2006
- Location
- England. Ish.
- Gender
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
-
2021-03-08, 01:04 PM (ISO 8601)
- Join Date
- Jul 2008
- Location
- Sweden
- Gender
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 adviceLast 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
-
2021-03-10, 05:48 PM (ISO 8601)
- Join Date
- Dec 2006
- Location
- Raleigh NC
- Gender
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
-
2021-03-10, 06:29 PM (ISO 8601)
- Join Date
- Mar 2007
- Location
- Oregon, USA
Re: SQL: RIGHT or LEFT JOIN more efficient
FeytouchedBanana eldritch disciple avatar by...me!
The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas
-
2021-03-11, 07:22 AM (ISO 8601)
- Join Date
- Mar 2012
- Location
- UK
- Gender
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.
-
2021-03-11, 09:17 AM (ISO 8601)
- Join Date
- Dec 2015
Re: SQL: RIGHT or LEFT JOIN more efficient
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.