2

I want to know that what is the use of right join exactly as we can get the same result from left join by interchanging the tables.

So let's take an example here -

Suppose i need to join two tables TAB_A and TAB_B with right join as below to get the result -

SELECT * FROM TAB_A RIGHT JOIN TAB_B

but i can also get the same result of query by using left join also instead of right join as below

SELECT * FROM TAB_B LEFT JOIN TAB_A

So my question is what is the purpose of right join in sql exactly, is there any performance related comparision or anything that can not be done by left join?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Govind
  • 71
  • 1
  • 11
  • 1
    Stick to left join. Forget about right join, it's too confusing to most people. (Many of them find left join hard enough to understand.) You never need right join, simply switch the tables and do a left join - same performance etc. – jarlh Mar 16 '16 at 10:23
  • hey mattia i did not ask about the difference here as i know what is the difference between both of them, here i just want to know about the use of right join as there should be some purpose of right join as we have it in sql – Govind Mar 16 '16 at 10:29
  • The question takes me to think about purpose of <, <=, >= too :) – Serif Emek Mar 16 '16 at 10:39
  • If someone can think of a simple example which would require both right and left join in the same query, that would make for a very nice answer. – Tim Biegeleisen Mar 16 '16 at 10:43
  • @TimBiegeleisen As far as I know every query that uses right join can be rewritten to use left join. – Shadow Mar 16 '16 at 10:48
  • @Shadow I don't doubt this, but perhaps there is an example where using `RIGHT JOIN` makes it easier to read and write. – Tim Biegeleisen Mar 16 '16 at 10:51
  • @TimBiegeleisen I have been told by a Japanese guy that for them right join is easier, since they are reading from right to left... – Shadow Mar 16 '16 at 11:04

3 Answers3

0

Suppose you need to do a double join like this:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.name = table2.name1
RIGHT JOIN table3 ON table2.position = table3.job;

that is when the RIGHT JOIN is useful...

Regards

White Feather
  • 2,733
  • 1
  • 15
  • 21
  • yes got it... that must be a reason for right join – Govind Mar 16 '16 at 11:26
  • 1
    Not really. You can easily rewrite this query using only `LEFT` or only `RIGHT` joins. – ypercubeᵀᴹ Mar 16 '16 at 12:06
  • @ypercubeᵀᴹ you are totally right (not left :)). You can rewrite only with LEFT or RIGHT join. The item as been extensively discussed in http://stackoverflow.com/questions/436345/when-or-why-would-you-use-a-right-outer-join-instead-of-left. Let say that RIGHT join make it easier if you already have a JOIN to modify it without rewrite and generally speaking make the language more complete for different way of thinking. – White Feather Mar 16 '16 at 15:12
0

In case of only tow table you can switch the tables to get the same result from right and left join. But if you are having more than two table and you need to put left join on few tables right join on few tables so in that case you need both the joins.

Sarvjeet Verma
  • 106
  • 7
-1

As stated in http://dev.mysql.com/doc/refman/5.7/en/outer-join-simplification.html

At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations.

Using RIGHT JOIN will cause parser conversions, but it should be negligible in pracitce.

piotrgajow
  • 2,880
  • 1
  • 22
  • 23