CODEWITHSUNDEEP
sqlsql-serveroracle-database
Kaja
Kaja

Reputation: 3057

How to force join order in SQL Server

When we join t1 to t2 to t3 to t4 we can go:

t3->t4->t2->t1
t1->t2->t3->t4
t4->t3->t2->t1
.....

Is there any way that we force optimizer in SQL Server to start from specific table in the join chain? Something like leading in Oracle

I am looking for a solution like leading (in Oracle) in SQL Server.

Upvotes: 1

Views: 5818

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If, in addition to the order of the joins, you know what types of joins, then you can be explicit:

SELECT *
FROM T1 HASH JOIN
     T2
     ON T1.ID = T2.T1_ID;

This enforces the ordering as well the type (see here).

If you want to specify the use of a particular type of join without forcing the order, then use option instead. And, use force order if you want the ordering but not the type. However, the SQL Server optimizer is usually pretty good, so force order is rarely needed.

Upvotes: 7

Husqvik
Husqvik

Reputation: 5809

There is FORCE ORDER hint:

SELECT * FROM T1 JOIN T2 ON T1.ID = T2.T1_ID OPTION (FORCE ORDER)

Upvotes: 3

Related Questions