INF: "OR" Clause Prohibits the Use of Merge or Hash Algorithms (197247)

SUMMARY

Hash and merge algorithms can be used in a join operation between two or more tables only if there is at least one qualified equality comparison between the join attributes. An OR clause disqualifies all equality comparisons between joined tables.

MORE INFORMATION

A query like the one below will always use nested loops to join tables:

   select * FROM discounts a INNER JOIN stores b
      ON a.stor_id = b.stor_id or b.stor_id like '78%'

If you attempt to force this query to use hash or merge you will get a Query Processor error. This is because the OR condition disqualifies a.stor_id = b.stor_id as a join predicate.

SQL Server Books Online notes that merge and hash joins can be used only if there is at least one equality (WHERE) clause in the join predicate.

A requirement of the hash and merge algorithm, however, is that if a row fails the equality portion of the predicate, it is considered a non-match. The non-matched rows are consequently unavailable for any further processing. But if there is an OR clause in the expression, a row may fail the equality portion of the predicate but still be a match from the other side of the OR clause (for example, "b.stor_id like '78%'").