Skip to Main Content
  • Questions
  • Table A Left Outer JOIN Table B VS Table B Right Outer JOIN A

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, prashant.

Asked: August 31, 2016 - 4:21 pm UTC

Last updated: September 02, 2016 - 7:48 am UTC

Version: 10.2.0.1.0

Viewed 1000+ times

You Asked

Is Table A LEFT OUTER JOIN TABLE B same as TABLE B RIGHT OUTER JOIN TABLE A in in terms of output and performance?
(Regardless of data content and size of table A, and table B)

and Chris said...

Well. The join is equivalent. i.e. they will return the same rows.

But if you use a select *, they will return the columns in a different order!

create table t1 as
  select rownum x from dual connect by level <= 2;
create table t2 as
  select rownum+1 x from dual connect by level <= 2;

select * from t1
left join t2
on   t1.x = t2.x;

X  X  
2  2  
1
 
select * from t2
right join t1
on   t1.x = t2.x;

X  X  
2  2  
   1

So strictly speaking they're not identical. To avoid this, explicitly specify the columns. Then you get the same output:

select t1.x, t2.x from t1
left join t2
on   t1.x = t2.x;

X  X  
2  2  
1
 
select t1.x, t2.x from t2
right join t1
on   t1.x = t2.x;

X  X  
2  2  
1


Do this and in both cases Oracle transforms the SQL to the following before executing it (run a 10053 trace to get this):

SELECT "T1"."X" "X","T2"."X" "X" 
FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" 
WHERE "T1"."X"="T2"."X"(+);


Thus confirming they are "the same".

Is this answer out of date? If it is, please let us know via a Comment