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

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%'").