Real time usage and difference between left outer join and right outer join

Question

If Left outer join is used(select table A left outer join table B), I can have null values in right table(Table B) when data is not matching with left table(Table A). If I change the select query order (select table B left outer join table A), now I can have null data in Table A. So, same operation can be performed by using left outer join. So, what is the use of right outer join? Please help me to get solution on this.

For two tables you can reverse the order of the tables and switch the join type from `left <-> right` and it will be semantically the same. When more than one table is involved this is not the case though. [I give an example of that here](http://stackoverflow.com/a/7313507/73226) — Martin Smith, Jul 07 '13 at 12:40

score 1 · Answer 1 · answered Jul 07 '13 at 12:40

1

left outer join and right outer join are, in a sense, redundant. You can write a query using only one of them.

They are both provided for the same reason that < and > are both provided. Sometimes one or the other makes more sense for a given logical operation.

As for me, I strive to write queries using only join and left outer join. The left outer join makes more sense to me, because it says "keep all the rows in the first table, along with matching rows in other tables". This doesn't mean that right outer join is wrong, just that different people understand things in different ways.

answered Jul 07 '13 at 12:40

Gordon Linoff

1,242,037
58
646
786

Indeed it true that you can do everything with `LEFT` and `INNER` joins and of course , but very few times I founded that mixing `LEFT`, `RIGHT` and `INNER` I could show a kind of _dependency flow_ in the query. those few times, I payed special attention to write down the explanation. I think that the remark was times longer that the joins themselves. Of course the Cartesian logic becomes a nightmare – Luis LL Jul 07 '13 at 13:01
@LuisLL . . . I commend you. I am very practiced in SQL with many years of experience writing quite complicated queries and working with other people's code. Yet, whenever I see different types of joins mixed in a `from` clause, I have stop and think hard about which records are coming from where and which are being filtered -- unless all the joins are the same. – Gordon Linoff Jul 07 '13 at 13:22
[known note](http://www.javacodegeeks.com/2011/07/funny-source-code-comments.html) -- when I wrote this, only God and I understood what I was doing. Now God only knows. I'm very experienced dealing with in SQL bizarre queries, and my experience show that I must dedicate time ahead in those things, because if not, a when I need to come back to them, I'll dedicate more time to convince myself that the query really does the work I intended then... – Luis LL Jul 07 '13 at 13:43

score 0 · Answer 2 · answered Jul 07 '13 at 12:45

The difference is simple – in a left outer join, all of the rows from the “left” table will be displayed, regardless of whether there are any matching columns in the “right” table. In a right outer join, all of the rows from the “right” table will be displayed, regardless of whether there are any matching columns in the “left” table. Hopefully the example that we gave above help clarified this as well.

Should I use a right outer join or a left outer join? Actually, it doesn’t matter. The right outer join does not add any functionality that the left outer join didn’t already have, and vice versa. All you would have to do to get the same results from a right outer join and a left outer join is switch the order in which the tables appear in the SQL statement.

score -1 · Answer 3 · answered Jul 07 '13 at 12:39

SELECT * from TableA LEFT JOIN TableB Same as SELECT * from TableB RIGHT JOIN TableA

SELECT * FROM TableA LEFT JOIN TableB All rows in TableA and matching rows in TableB. If no matching row is found in TableB then all the columns of TableB will be replaced by null

Example: TableA rows 1 2 3 Table B rows 2 3 4

TableA LEFT JOIN TableB will give the following tuples: (1 NULL) (2 2) (3 3)

TableA RIGHT JOIN TableB will give the following tuples: (2 2) (3 3) (NULL 4)

TableA OUTER JOIN TableB the following tuples: (1 NULL) (2 2) (3 3) (NULL 4)

Real time usage and difference between left outer join and right outer join

3 Answers3