Join
Last updated
Was this helpful?
Last updated
Was this helpful?
The Join action is one of the common methods to combine tables. It is to merge tables side by side based on the key columns. Specifically, Join compares the values of the key columns in two tables. Rows with the same value in the key columns will be concatenated together. Thus, Join add more columns to the original table and let your table become wider.
The Join action allows you to merge other tables into the current table based on some columns. You can go to any table and click the Join action under the Transformation tab. Join requires four pieces of input:
In this field, you can choose a table to merge into the current table. Note that you can only choose the tables within the same project. For example, the project below has three tables, and thus you can see these three tables in the dropdown when selecting tables
Join keys are the columns shared between two tables. They can tell the system how to match the corresponding rows. You can specify one or more join keys at a time.
The following example is to define the join key as the county_fips_code column, so Acho will combine two tables based on this column.
In the last step, you can optionally choose to bring which columns from the right table to the current table.
In the example as shown below, there are 6 columns in the right table (make_use_by_county) . Here we only check three columns (sometimes, frequently, and always) , so Acho will only append these three columns from the right table to the current table.
Acho currently supports four types of joins:
Here we use an example to explain the difference between these join types. Suppose you have two tables as shown below and the only difference between the two tables is the last row.
Now you want to combine two tables based on the State column.
Inner Join: returns a table that contains values that have matches in both tables. When a value doesn't match across both tables, it is dropped entirely.
Left Outer Join: returns a table that contains all values from the left table and corresponding matches from the right table. When a value in the left table doesn't have a corresponding match in the right table, you will see null values in the data grid from the right table.
Right Outer Join: returns a table that contains all values from the right table and corresponding matches from the left table. When a value in the right table doesn't have a corresponding match in the left table, you see null values in the data grid from the right table.
Full Outer Join: returns a table that contains all values from both tables. When a value from either table doesn't have a match with the other table, you see a null value in the data grid.
I filled all input fields in the Join action but the system showed an error "Invalid Query. Please check your input".
Before choosing a table to join, you have to define the join method first. The join type is the relationship between two tables. Acho currently supports four types of joins: Inner Join, Left Outer Join, Right Outer Join and Full Outer Join. (To see the difference between join methods, please see .)
When you face this situation, please check whether your join keys in both tables are in the same data type. You can check the data type of the join key by clicking the dropdown in Select Join Keys. Take the following as an example. The join key in the left table is String, so the right join key should also be String. If the right key is in another data type, such as Integer, the Join action will run into an error. In this situation, you can use the action to change the data type.
