Order to the processing of JOIN statements? (1 Viewer)

[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.

 

[Pat Hartman]

For left joins, I'm thinking it probably doesn't matter but for inner joins it could. Left joins don't reduce the # of rows in the recordset but equi-joins can. The query analyzer should take things like this into consideration so I'm not sure you could even impact it in SQL Server. In Access, you can impact it by creating separate queries and joining those rather than using a single query with a nested join or a subselect. There is one join in Access that acts differently from what you would expect if you were a SQL Server user. If your criteria is on the right-side table, the left join will work like an inner join. To make Access respect the "left", you need to create a separate query to apply the right-side criteria. Then join that query to the left table using a left join.

If you want to know the actual answer to the question, use SQL Server Profiler or whatever the new tool is called so you can see the execution plan.

 

[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.