1

I'm studying SQL, I made no test at all, so I'm sorry for my stupid question. apparently (in my guess LOL) LEFT and RIGHT join can be equivalent inverting the tables (operands). is this right?

if yes, so why I should prefer to write this:

SELECT * FROM aaa LEFT OUTER JOIN bbb ON aaa.x = bbb.x

instead of this:

SELECT * FROM bbb RIGHT OUTER JOIN aaa ON aaa.x = bbb.x
skyline26
  • 1,994
  • 4
  • 23
  • 35
  • Order in the `ON` clause does not matter -- what matters is what is being OUTER JOIN'd, and how (LEFT, RIGHT, FULL). Personally, I don't see the use for `RIGHT`, I always use `LEFT`... – OMG Ponies Mar 07 '12 at 06:21
  • i know that isn't difference between a=b and b=a, i'm talking about the join operands – skyline26 Mar 07 '12 at 06:22
  • As long as table **and** operand is reversed, they are equivalent. Whatever is OUTER JOIN'd, becomes optional (meaning, any columns from that reference could be `NULL` if there's no data for the relationship defined). – OMG Ponies Mar 07 '12 at 06:23
  • Same reason `NATURAL JOIN` does -- ANSI isn't perfect, and it helps to indicate direction. Plus, can't have LEFT without RIGHT. That's like saying you believe in Satan, but not God... – OMG Ponies Mar 07 '12 at 06:26
  • yes but why is the direction required since i can invert the table names? For instance >= and <= operators exist for programmers convenience, because it is really needed only one of them, since a >= b === b<=a. so RIGHT and LEFT are made for convenience (of the parser, compiler, programmer) or it is any actual difference? – skyline26 Mar 07 '12 at 06:31
  • Dont forget to mark ansswer as accpeted if you got the info you want – Pranay Rana Mar 07 '12 at 06:32
  • @PranayRana I surely will mark accept when I will have the info i'm looking for! – skyline26 Mar 07 '12 at 06:34

2 Answers2

8

Best way i found is have look to below image clear out you doubt

Orignal Ans at : How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?

alt text

Community
  • 1
  • 1
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • yes but this doesn't answer to my question: pick the first top left example of your image (SELECT * FROM aaa LEFT JOIN bbb ON aaa.x=bbb.x) is equivalent to (SELECT * FROM bbb RIGHT JOIN aaa ON aaa.x=bbb.x) or not? – skyline26 Mar 07 '12 at 06:21
  • so... again, what is the actual difference, if there is any, between LEFT and RIGHT, since i can invert table names? – skyline26 Mar 07 '12 at 06:35
  • @toPeerOrNotToPeer - it depends on your interest but the way you written it doent make any difference – Pranay Rana Mar 07 '12 at 06:37
  • and in more complex queries, left and right can make difference in the result, or it is in any case something that is available for the programmer's convenience? – skyline26 Mar 07 '12 at 06:39
  • @toPeerOrNotToPeer - as i said it depends on your interest..that you have to choose which you want to use . – Pranay Rana Mar 07 '12 at 06:40
3

In many scenarios, you have to join more than 2 tables - like stored procedures, views or queries you need data from more than 2 tables- . In these scenarios, you need to choose a start point - in this case a table - for your query.

In one, table "aaa" will be suitable to start the query with, in other "bbb" will be better.

That's simply why LEFT and RIGHT joins exist.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
Can Yener
  • 179
  • 1
  • 5