Join Outer
Performs an outer join of the Left Table and the Right Table on the columns specified in the Left Column Name and the Right Column Name fields. The joined table contains all columns from the Left Table followed by all columns from the Right Table, and contains all rows where the value in the Left Column exactly matches the value in the Right Column, plus additional rows according to the Outer Join Type, which may be left, right, or full.
Usage notes
In a left outer join, the result table includes all the rows from the left table; in a right outer join it includes all the rows from the right table, and in a full outer join it includes all the rows from both tables. In any row where there is no match for the join column value, the cells from the other table contain null values. (Null values are represented as blank for strings, 0 for integers and longs, NaN for floats and doubles, and NULL_DATE for dates.)
Left Column Name and Right Column Name can each specify a semicolon-separated list of n column names, in which case a match occurs if the ith value in Left Column Name exactly matches the ith value in Right Column Name, for all i between 1 and n, inclusive.
For a full join or right join, if the Left Table is null, the result is Right Table. For a full join or left join, if Right Table is null, the result is Left Table. In all other cases the result is null.
Arguments
The function has the following fields:
Left Table: The first table to be joined.
Right Table: The second table to be joined.
Left Column Name: (Optional) The column or columns in the left table to be joined with the column or columns specified in the
Right Column Name field. If this field is left blank, the row name, up to the first : if it contains a :, is used instead of a column value.
Right Column Name: (Optional) The column or columns in the right table to be joined with the column or columns specified in the
Left Column Name field. If this field is left blank, the row name, up to the first : if it contains a :, is used instead of a column value.
Outer Join Type: Specified as
left,
right, or
full, which may be abbreviated to their first letters. If this field is left blank a full outer join is performed.
This function returns a table.