The Join node is used to create joins between two tables, adding the join to the database schema. The Join node can be connected to any Select node (excluding Multi Select), Preparation node, or Column Operation node. The Join function combines columns horizontally, based on matching columns and according to the selected join type.
The Join node can be used to join tables from the data source, or to join data source tables with new tables generated by data cleansing and preparation functions. For example, both the Date Range and Summarize functions generate new tables; these can then be joined to the original table from the data source. At other times, you may have tables showing similar information that might be more user friendly for data analysts if combined into one table.
Note: Relationship 'joins' between tables can be defined during the Data Model phase. These joins will be added at runtime during the query, rather than being stored in the database.
Joining two tables
From the Data Flow:
-
Drag the Join node onto the canvas and connect it to the two tables that are being joined.
-
Select the Join node on the Canvas, and go to the Properties panel to configure the join:
- Resulting Table Name: Name the new table.
- Join Type: Choose the join type from the drop-down at the top of the Join Node section. For more information, see Join Types.
- Join Columns: For each table, select the column where the join is applied. For more information about the table options, see Join Details.
Join Node properties
Join Types
Choose from the following join types:
- Full Outer Join: Returns all rows from both tables.
- Inner Join: Returns all rows from both tables based on a matching column. Only returns rows where the matching column's values are identical.
- Left Outer Join: Returns all rows from the first / left table, and matching rows from the second / right table.
- Right Outer Join: Returns all rows from the second / right table, and matching rows from the first / left table.
- Cross Join: Returns a result set by multiplying the number of rows in the first table by the number of rows in the second table. The result is a table that returns all possible combinations of all rows from both tables.
Example Joins
Example 1: Join Tables
In this example, we have two tables containing manufacturer information: Manufacturer Details (blue highlight below) and Manufacturers (orange highlight).
We want to combine the columns in each of these two tables to produce one table that looks like this:
To achieve this, we add the Join node to the Data Flow and connect it to both tables using a Left Outer Join (blue arrow below), making sure to connect the tables in the order we want to merge them:
This produces the following results set, which includes two identical Manufacturer columns (green), one from each table:
Finally, to show only one Manufacturer column, we need to open the Column Selection window from the Properties panel and clear the checkbox alongside one of Manufacturer columns (purple arrow below), leaving us with the required results set:
Example 2: Join Summarize Columns to Source Table
In this example, the Summarize node is added to the table to produce new "summarized" columns:
The new Summarize columns are then joined to the original table by connecting the Join node to both the Table and the Summarize tables, and adding a Right Outer Join:
Copy the join script
Click Copy to copy the script that underlies this join to your clipboard. Tip: You can use this script when testing in the database.
Related information
Common Properties
There are a number of fields that are present in the Properties panel when you have any of the preceding nodes selected on the canvas. These fields include Result Properties, Column Selection, Set Variable Values, and Metadata.
- Click here for more details about the Common Properties