Skip to main content

Table.join

joinrightjoin_kind onright_prefixon_problems

Group: Calculations
Aliases: hlookup, lookup, merge, vlookup, xlookup

Documentation​

Joins two tables according to the specified join conditions.

Arguments​

  • right: The table to join with.
  • join_kind: The Join_Kind for the joining the two tables. It defaults to Left_Outer.
  • on: A set of conditions on which to correlate rows from the two tables. If multiple conditions are supplied, rows are correlated only if all are true. By default, the join is performed on the first column of the left table correlated with a column in the right table with the same name.
  • right_prefix: The prefix added to right table column names in case of name conflict.
  • on_problems: Specifies how to handle problems if they occur, reporting them as warnings by default.

Returns​

  • Returns a new table with the rows from the two tables joined according to the specified conditions.

Examples​

Inner Join on Name from a table with Name and Location to a table with​

Name and Age.

      left_table = Table.from_rows ["Name","Location"] [["John", "Massachusetts"],["Paul","London"]]
      right_table = Table.from_rows ["Name","Age"] [["John", 20],["Paul",30]]
      join_table = left_table.join right_table ..Inner [..Equals Name]

Returns a Table

NameLocationRight NameAge
JohnMassachusettsJohn20
PaulLondonPaul30

Left Outer Join on Name from a table with Name and Location to a table​

with Name and Age.

      left_table = Table.from_rows ["Name","Location"] [["John", "Massachusetts"],["Paul","London"],["Ringo","Paris"]]
      right_table = Table.from_rows ["Name","Age"] [["John", 20],["Paul",30]]
      join_table = left_table.join right_table ..Left_Outer [..Equals Name]

Returns a Table

NameLocationRight NameAge
JohnMassachusettsJohn20
PaulLondonPaul30
RingoParisNothingNothing

Errors​

  • If a column name cannot be found, a No_Such_Column is reported and an empty result is reported.
  • If a column index is invalid, an Index_Out_Of_Bounds is reported and an empty result is reported.
  • If there are column names that are clashing between the two tables, a Duplicate_Output_Column_Names is reported and the columns from the table are renamed as described below.
  • If a join condition correlates columns whose types are not compatible (for example comparing numeric types with text), an Invalid_Value_Type is reported.
  • If decimal columns are joined on equality, a Floating_Point_Equality is reported.

Remarks​

Join Kind​

Enso supports the following join kinds:

  • ..Inner - Only the records that matched from the left and right tables.
  • ..Left_Outer - all records from the left table, and the matched values from the right.
  • ..Right_Outer - all records from the right table, and the matched values from the left.
  • ..Full - all records, matched or unmatched.
  • ..Left_Exclusive - only the records that did not match from the left table.
  • ..Right_Exclusive - only the records that did not match from the right table.

It is possible to do a cartesian join (where every row from the left table is paired with every row from the right table) as well, by using the cross_join function.

Column Renaming​

If columns from the two tables have colliding names, a prefix (by default Right_) is added to the name of the column from the right table. The left column remains unchanged. It is possible that the new name will be in use, in this case it will be resolved using the normal renaming strategy - adding subsequent _1, _2 etc.

Nothing Equality​

When comparing Nothing values, filter follows the ANSI SQL conventions that Nothing == Nothing results in a Nothing. This means that in a join this Nothing values will not match each other.

Result Ordering​

The ordering of rows in the resulting table is not specified.

Joining on equality of columns with the same name​

When performing an Inner join on two columns with the same name and an equality condition, only one copy of column will be included in the output (as these two columns would have the exact same content, so they would be redundant).

Same-name column join shorthand​

As a shorthand, providing a column name or a list of column names allows to join the two tables on equality of corresponding columns with the same name.

So table.join other on=["A", "B"] is a shorthand for:

table.join other on=[..Equals "A" "A", ..Equals "B" "B"]