All Forums Database
chinna557 3 posts Joined 03/12
06 Mar 2012
Order of joins in the SQL

I have three tables named Table A has 100 rows , Table B has 50 rows, Table C has 255 Rows.

There are 25 matching records between Table A and Table B.

 

Now I wants to know below two queries yield same result or different result?

 

Query1:

Sel A.col,B.col,C.col

From Table A INNER JOIN Table B

ON A.col= B.Col

LEFT JOIN Table C

ON A.Col=C.Col

 

 

Query2:

Sel A.col,B.col,C.col

From Table A LEFT JOIN Table C

ON A.col= C.Col

INNER JOIN Table B

ON A.Col=B.Col

 

Please help me to know whats the counts of each Query and result set is same with both the queries.

ulrich 816 posts Joined 09/09
06 Mar 2012

In generall should the join order not change the result (as long as you do not change logic by changing the order and using different join conditions). The optimizer trys to find the most efficient order of processing.

So I would expect the same results.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Stefans 38 posts Joined 02/12
07 Mar 2012

Both the queries will produce similar results..

Stalin

ulrich 816 posts Joined 09/09
07 Mar 2012

@Stefans: similar is not the same...
 

But it is the same!

Check below sample code.

Both SQLs will show you the same explain and the same results.

create table join_order_test_a (col integer);
create table join_order_test_b(col integer);
create table join_order_test_c (col integer);

insert into join_order_test_a 
select calendar_date - current_date as id
from sys_calendar.calendar
where id between 1 and 100;

insert into join_order_test_b 
select calendar_date - current_date as id
from sys_calendar.calendar
where id between 76 and 125;


insert into join_order_test_c 
select calendar_date - current_date as id
from sys_calendar.calendar
where id between 1 and 510
and id mod 2 = 1;

select count(*)
from join_order_test_a
;

select count(*)
from join_order_test_b
;

select count(*)
from join_order_test_c 
;


Sel A.col,B.col,C.col

From join_order_test_a  A INNER JOIN join_order_test_B  B

ON A.col= B.Col

LEFT JOIN join_order_test_c C 

ON A.Col=C.Col
minus
Sel A.col,B.col,C.col

From  join_order_test_a A LEFT JOIN  join_order_test_c C

ON A.col= C.Col

INNER JOIN  join_order_test_B B

ON A.Col=B.Col
;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Stefans 38 posts Joined 02/12
07 Mar 2012

Ulrich - I accept...Its same result and not similar...

Stalin

chinna557 3 posts Joined 03/12
11 Mar 2012

Thanks Ulrich, for your solution.

 

Thanks stefans for your comments on the same.

reddygn1 3 posts Joined 03/12
26 Mar 2012

what  about performance of the Q's?

if Query has more join conditions at this time what about performance ?

 

ulrich 816 posts Joined 09/09
27 Mar 2012

@reddygn1

What is your question? Can you give examples? And maybe open a different thread...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.