CODEWITHSUNDEEP
sqlsql-server-2008join
Snehanjali Sahoo
Snehanjali Sahoo

Reputation: 3462

Order of table in JOIN

In which order tables are accessed in the joining of tables in sql server ?

Can we force some specified order ?

Upvotes: 3

Views: 177

Answers (2)

GarethD
GarethD

Reputation: 69759

Yes, use the FORCE ORDER query hint e.g.:

SELECT  *
FROM    C
        INNER JOIN B
            ON B.BID = C.BID
        INNER JOIN A
            ON A.AID = B.AID
OPTION (FORCE ORDER);

This ensures that the join order indicated by the query syntax is preserved during query optimization. In the below two execution plans the top plan does not have the query hint where as the bottom one does:

enter image description here

You can see that in the first the optimiser applies the order it thinks best (A then B then C) whereas in the second the original order is maintained (C then B then A). As a general rule I would say unless you understand the optimiser well enough to know why the optimiser is picking a certain plan, and why another plan would be more efficient, and why the optimiser has not picked this better plan then I would stay away from forcing execution plans by using hints, the optimiser usually knows best, and where it doesn't it is more likely to be stale statistics or fragmented indexes etc, rather than something that should be fixed by forcing an execution plan.

The schema and queries above are on SQL Fiddle.

Upvotes: 4

SinisterPenguin
SinisterPenguin

Reputation: 1618

If I understand the question properly you may find CTE's or temp tables are a good way to manage complex join scenarios'.

I find them useful to "pre-filter" & control data from joined tables & make queries easier to understand.

Though if you could explain WHY you want to control join order people may be able to help a bit more.

Upvotes: 0

Related Questions