Order to the processing of JOIN statements?

[Isaac]

There's no specific, hard-fixed order to how SQL Server processes the JOIN statements right?

i.e. it wouldn't matter which JOIN statement came first?

(I am referring to ENTIRE join statements, such as inner join table2 t2 on t2.clientit = t1.ID, not referring to the order of the tables inside the join statement b/c of course that matters)

The optimizer just decides the best order for the purpose of efficiency?

 

[Minty]

I know that I tend to write joins in the order they would appear if I was using the QBE design window.
My understanding is that the optimiser is clever enough to work it out if they are inner joins.

There is a sort of related discussion here:

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated.....

dba.stackexchange.com

And a more specific set of answers here:

Does the join order matter in SQL?

Disregarding performance, will I get the same result from query A and B below? How about C and D? ----- Scenario 1: -- A (left join) select * from a left join b on <blahblah> ...

stackoverflow.com

 

[The_Doc_Man]

Because most people that I've seen with JOIN issues normally do the JOIN via parentheses, the order of the JOIN is specified by the order of parentheses.

SELECT yada-yada-yada FROM ( A JOIN B ON A.yada = B.yada ) JOIN C ON B.yada = C.yada ;

 

[Isaac]

Because most people that I've seen with JOIN issues normally do the JOIN via parentheses, the order of the JOIN is specified by the order of parentheses.

SELECT yada-yada-yada FROM ( A JOIN B ON A.yada = B.yada ) JOIN C ON B.yada = C.yada ;

I'm afraid I have not explained myself very well, sorry about that. What I mean is more like this, do these two statements make any difference in , perhaps, some kind of efficiency (I know they make no difference functionally):

select
    *
from
    Table1 t1
    left join ref1 r1 on r1.id=t1.id
    left join ref2 r2 on r2.id=t1.id

versus

select
    *
from
    Table1 t1
    left join ref2 r2 on r2.id=t1.id
    left join ref1 r1 on r1.id=t1.id

PS and by posting in SQL Server I'm trying to restrict it mostly to that context, where all of those numerous superfluous parenthesis Access coats everything with aren't often needed

 

[Josef P.]

The SQL server does not always execute the SQL statements in the written order if it "thinks" that the statistics know a better order.

However, you can force the SQL server to do this (which will very rarely be wise):
Option (force order)

FORCE ORDER​

Specifies that the join order indicated by the query syntax is preserved during query optimization. Using FORCE ORDER doesn't affect possible role reversal behavior of the Query Optimizer.

 

[ebs17]

The optimizer just decides the best order for the purpose of efficiency?

So it is.
As far as I know, the individual JOINs and the individual criteria in the WHERE part are initially equivalent in the same query level, regardless of the specified order.
The optimizer is based on existing table statistics, implementation of index usage, etc.