Avoiding join predicates on expressions

Using expressions on join predicates can limit the join method used.

A hash join method will be considered for join predicates with an expression, as long as none of the following types of functions are used:
  • A function that reads or modifies SQL data
  • A function that is non-deterministic
  • A function that has an external action
  • A function that uses a scratchpad
If columns from both operands of the associated join are used on the same side of the join condition, hash joins are also considered, but cannot be chosen in most cases.

If a hash join cannot be used, then a potentially slower nested loop join will be used instead, and in these cases the cardinality estimate might be inaccurate.

An example of a join with an expression that is considered for a hash join:
WHERE UPPER(CUST.LASTNAME) = TRANS.NAME
An example of a join with expression that would not benefit from a hash join, but would use a nested loop join instead:

WHERE RAND() > 0.5