user5827762 February 2016

Is the order of multiple INNER JOIN-s related with the tables relashionship?

I want to join some tables using INNER JOIN statement.Some of them have the many to many relashionship ,some one to many and some one to one. I want to know does the order of INNER JOIN-s statement matters and is it related with the type of relashionship(One to one,one to many etc.)? So does these three codes below output the same result?

SELECT ....
FROM table1
INNER JOIN (table2 INNER JOIN table3 ON table2.col=table3.col)
ON table1.col=table2.col


SELECT ....
FROM table1
INNER JOIN (table2 INNER JOIN table3 ON table2.col=table3.col)
ON table1.col=table3.col

SELECT ....
FROM table2
INNER JOIN (table1 INNER JOIN table3 ON table1.col=table3.col)
ON table3.col=table2.col
And can I replace the INNER JOIN of two tables with this code below?So does this code below represents the inner join of table 1 and table2?

SELECT ...
FROM table1,table2
WHERE (table1.col=table2.col)

Answers


Jonathan February 2016

It makes absolutely no difference to the results. Because you are using only inner joins, only the matches in all three tables will show.

If you were to use a LEFT OUTER join and an INNER join in one query, you could vary the resultsets.


par February 2016

For INNER JOIN it will give same result but with other type of join it will give different result. for example:

SELECT ....
FROM table1
LEFT JOIN (table2 INNER JOIN table3 ON table2.col=table3.col)
ON table1.col=table2.col

above is equivalent to

SELECT ....
FROM table1
LEFT JOIN (
    Select table2.col 
    From table2 INNER JOIN table3 ON table2.col=table3.col
    ) tbl
ON table1.col=tbl.col

First it will do INNER JOIN of table2 & table3 then the table1 will left joined with the result


Alex Lebedev February 2016

Exactly order of joins is not matter.

Better to use

select ...
from table1
inner join table2 on table2.col=table1.col
inner join table3 on table3.col=table1.col

Yes, INNER JOIN's could be replaced with

WHERE t1.col=t2.col

And SQL plan will be the same. But if there are other filters in where condition - will mix.

Also, if there is additional join conditions - better to filter out all not required records first.

Post Status

Asked in February 2016
Viewed 2,824 times
Voted 12
Answered 3 times

Search




Leave an answer