It is bizarre, and entirely counter-intuitive to most people (though it is at least documented these days). There is often considerable consternation when an attempt to enforce a particular physical join type between two tables results in a completely stupid plan because of the implied FORCE ORDER.

The query optimizer in SQL Server uses the Cascades framework - a cost-based top-down optimizer that works by matching patterns (logical tree fragments) using rules. There are nearly 400 of these rules - some of which implement logical operations as physical operators (e.g. implementing a relational JOIN using sort-merge), some of which generate logically-equivalent relational alternatives (e.g. pushing an aggregate below a join or considering a different join order), and still others that remove redundancies (e.g. removing empty tables or aggregates where the input is known to be unique).

The second factor is how to interpret A INNER LOOP JOIN B, or C RIGHT HASH JOIN D. Did the query writer intend to drive the query from table A in the first case, and to build the hash table on input C in the second case? In most cases where the query writer wishes to enforce a physical join type, this heuristic is a good one. To meet the writer's expectation, SQL Server needs to guarantee that it will produce a plan where A drives a nested loops join to B, and the hash join between C and D has C as the build input and D as the probe.

Now, in principle the optimizer could make that guarantee in one of two ways. First, it could somehow fix the join in place and try to optimize around it. That just isn't practical for an extensible general-purpose optimizer based on Cascades - all kinds of horribly specific and hard-to-maintain code would have to be written. Instead, SQL Server guarantees the physical join type (LOOP, HASH, MERGE, REMOTE) by setting a required physical property on the logical JOIN in the query tree. This allows other physical join type rules to match elsewhere in the final plan, but only the one specified physical implementation can match the required property on the join in question.

Meeting the input-order requirement is more difficult to generalize. One consideration is that the inputs to the join may be an arbitrarily complex tree rather than a simple table. Anyway, rather than deal with all the complexity, SQL Server uses a Big Hammer to enforce join order: all the simplification and transformation rules that could change the join order from that shown in the logical tree produced by the parser and algebrizer, are disabled. Not surprisingly, this can have all sorts of unexpected effects on the final plan, but it does guarantee the join order.

As an example, consider this join:

SELECT th.ProductID

FROM Production.Product AS p

JOIN Production.TransactionHistory AS th ON

th.ProductID = p.ProductID

That results in a simple scan of the history table, because there are constraints that guarantee that a single matching product record exists. If we now force a join type:

SELECT th.ProductID

FROM Production.Product AS p

INNER LOOP

JOIN Production.TransactionHistory AS th ON

th.ProductID = p.ProductID

The resulting plan contains a nested loops join as we specified, and the pointless join can no longer be eliminated (that simplification rule is switched off temporarily). To see that the effect is plan-wide, we can UNION ALL the query to itself, specifying the join hint in only one part:

SELECT th.ProductID

FROM Production.Product AS p

INNER LOOP

JOIN Production.TransactionHistory AS th ON

th.ProductID = p.ProductID

UNION ALL

SELECT th.ProductID

FROM Production.Product AS p

JOIN Production.TransactionHistory AS th ON

th.ProductID = p.ProductID

Now, neither query is simplified, even though there's logically no reason not to in the second case. A serial plan shows a loops join for the top query and a merge join for the lower one; a parallel plan chooses a hash join instead of merge.