Order of joins in the SQL

Database

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.

7 REPLIES
Supporter

Re: Order of joins in the SQL

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.

Enthusiast

Re: Order of joins in the SQL

Both the queries will produce similar results..

Supporter

Re: Order of joins in the SQL

@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
;

Enthusiast

Re: Order of joins in the SQL

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

Re: Order of joins in the SQL

Thanks Ulrich, for your solution.

Thanks stefans for your comments on the same.

Re: Order of joins in the SQL

what  about performance of the Q's?

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

Supporter

Re: Order of joins in the SQL

@reddygn1

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