Skip to Main Content
  • Questions

  • How Oracle Database gets the data from multiple left joins

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajendra.

Asked: February 12, 2019 - 1:48 pm UTC

Last updated: February 12, 2019 - 3:07 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I came across a scenario today.

table1
id integer
name varchar

table2
id integer
name varchar
designation varchar

table3
id integer
name varchar
relation_status varchar

I have the query as below which is working fine


select * from table1 
left outer join table2 
on  table1.id = table2.id
and table1.name = table2.name
left outer join table3
on table1.id = table3.id
and table1.name = table3.name


when i change the above query as below strangely i am getting a different result

select * from table1 
left outer join table2 
on table1.id = table2.id
and table1.name = table2.name
left outer join table3
on table1.id = table2.id
and table1.name = table3.name


I am unable to find out the reason. Can you please explain me how the multiple left outer joins works

Thanks
Raj

and Chris said...

I'm not sure why you think that's strange. The second query doesn't join table3.id to any of the columns in the other tables. Whereas the first does.

If you strip out the name columns from the joins, it's more obvious that you're not joining the third table to anything!

Which obviously is going to give different results than when you do join table3 to another:

create table t1 (
  c1 int
);
create table t2 (
  c2 int
);
create table t3 (
  c3 int
);

insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t1 values ( 3 );

insert into t2 values ( 1 );
insert into t2 values ( 2 );

insert into t3 values ( 1 );
insert into t3 values ( 4 );

commit;

select * from t1 
left outer join t2 
on  t1.c1 = t2.c2
left outer join t3
on t1.c1 = t3.c3;

C1   C2       C3       
   1        1        1 
   2        2   <null> 
   3   <null>   <null> 

select * from t1 
left outer join t2 
on  t1.c1 = t2.c2
left outer join t3
on t1.c1 = t2.c2;

C1   C2       C3       
   1        1        1 
   1        1        4 
   2        2        1 
   2        2        4 
   3   <null>   <null>

Rating

  (1 rating)

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

Comments

Rajendra Kalepu, February 13, 2019 - 6:35 am UTC

Thank you very much for clear explanation
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.