join-condition

join-condition specifies the conditions of a join that is used in a query.

join-condition

For INNER, LEFT OUTER, and RIGHT OUTER joins:

>>-search-condition--------------------------------------------><

For FULL OUTER joins:

   .-AND-------------------------------------------.   
   V                                               |   
>>---full-join-expression--=--full-join-expression-+-----------><

full-join-expression:

>>-+-+-column-name-------+--------------------------------------------+-><
   | |               (1) |                                            |   
   | '-cast-function-----'                                            |   
   |                                   .--------------------------.   |   
   |                                   V                          |   |   
   '-COALESCE--(-+-column-name-------+---+-,--column-name-------+-+-)-'   
                 |               (1) |   |                  (1) |         
                 '-cast-function-----'   '-,--cast-function-----'

Notes:

cast-function must only contain a column and the casting data type must be a distinct type or the data type upon which the distinct type was based.

For INNER, LEFT OUTER, and RIGHT OUTER joins, the join-condition is a search-condition that must conform to these rules:

With one exception, It cannot contain any subqueries. If the join-table that contains the join-condition in the associated FROM clause is composed of only INNER joins, the join-condition can contain subqueries.
Any column that is referenced in an expression of the join-condition must be a column of one of the operand tables of the associated join operator (in the scope of the same joined-table clause).

For a FULL OUTER (or FULL) join, the join-condition is a search condition in which the predicates can only be combined with AND. In addition, each predicate must have the form 'expression = expression', where one expression references only columns of one of the operand tables of the associated join operator, and the other expression references only columns of the other operand table. The values of the expressions must be comparable. Start of change Implicit cast between numeric and string data types is not supported for FULL OUTER join. End of change

Each full-join-expression in a FULL OUTER join must include a column name or a cast function that references a column. The COALESCE function is allowed.

For any type of join, column references in an expression of the join-condition are resolved using the rules for resolution of column name qualifiers specified in Resolution of column name qualifiers and column names before any rules about which tables the columns must belong to are applied.