• In the join condition following the ON keyword, predicates can be combined only with AND. Each predicate has the form expression operator expression. For FULL JOIN, the only valid operator is '='. For other joins (LEFT, RIGHT, INNER) all operators are valid (e.g. >, <, <>, >=, <=, =). If you think about this is an onerous restriction, try to remember the last time that you coded a join predicate that violated it.

• Remember that you can specify all the local predicates you desire in a WHERE clause following the ON clause.

• Remember that A = B is false if both A and B are null. However,

  COALESCE(A,'NOTHING') = COALESCE(B,'NOTHING')

  is true if both A and B are null. So, if you propose to join on columns A and B, and both columns allow nulls, and you want to pair up null values (which could yield ridiculous results, but that's another matter), use COALESCE (or VALUE) to specify a join predicate of

  COALESCE(A,'NOTHING') = COALESCE(B,'NOTHING').

  This assumes, of course, that 'NOTHING' is not a valid data value for column A or column B.