Overview
Use the Join gem to combine related data from two or more datasets.
Common use cases include:
- adding customer details to order records
- combining user activity with account information
- enriching datasets with lookup tables
- matching records between systems
The Join gem matches rows using one or more join conditions, such as matching customer IDs or order numbers.
The gem has a corresponding interactive gem example. See Interactive gem
examples to learn how to run sample pipelines
for this and other gems. How to choose a join type
Choose:
- Inner Join to keep only matching rows.
- Left Join to keep all rows from the first dataset.
- Right Join to keep all rows from the second dataset.
- Full Outer Join to keep all rows from both datasets.
- Cross Join to combine every row from both datasets.
Common join examples
| Goal | Recommended join type |
|---|
| Keep only matching records from both tables | Inner Join |
| Keep all records from the left table | Left Join |
| Keep all records from both tables | Full Outer Join |
| Combine every row from both tables | Cross Join |
| Port | Description |
|---|
| in0 | The first input table in the join. |
| in1 | The second input table in the join. |
| inN | Optional: Additional input table for the join. |
| out | A single table that results from the join operation(s). |
+ next to Ports.
Parameters
To configure the Join gem, you need to define join conditions and select the columns that will appear in the output table.
Join conditions
You can add one or more join conditions to the gem depending on the number of input tables added. Rows are matched using shared values, such as customer IDs, order IDs, or email addresses.
| Parameters | Description |
|---|
| Join type | The different join types you can choose from. These may vary by SQL provider. Learn about different join types below. |
| Join condition | The condition that matches rows between tables. |
If you want to use a type of join that is available in your SQL warehouse, you can type the name
of that join directly in Prophecy.
Expressions
| Parameters | Description |
|---|
| Expressions | Selects which columns appear in the output dataset. If left empty, Prophecy passes through all the input columns without any modifications. |
Example
Assume you have two tables: orders and customers. You want the orders table to include customer information, so you need to join the tables based on customer ID. You only want to preserve records in the output that have a match. To do so:
- Connect orders to in0 and customers to in1.
- Choose Inner Join as the join type.
- If using a visual expression, use the following join condition: in0.CustomerID equals in1.customer_id
- If using the code expression, use the following SQL join condition: in0.CustomerID = in1.customer_id
- Leave the Expressions tile empty.
- Save and run the gem.
Common issues
Duplicate rows after joining
Duplicate rows can occur when multiple rows in one table match the same row in another table.
Verify that:
- the join keys are unique when expected
- the correct join type is selected
- duplicate records do not exist in the input datasets
Missing records in the output
Rows may be excluded depending on the selected join type.
For example:
- Inner Join keeps only matching rows
- Left Join keeps all rows from the left table
- Full Outer Join keeps all rows from both tables
Null values in joined columns
Rows with NULL join keys may not match other rows.
Ambiguous column names
If both datasets contain columns with the same name, rename or qualify columns to avoid ambiguity.
Join types
Suppose there are two tables, Employees and Departments, with the following contents:
Employees
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_ID |
|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 30 |
| 4 | David | NULL |
| 5 | Eve | 20 |
Departments
| DEPARTMENT_ID | DEPARTMENT_NAME |
|---|
| 10 | HR |
| 20 | Engineering |
| 30 | Sales |
| 40 | Marketing |
INNER JOIN
Inner Join will return columns from both the tables and only the matching records as long as the condition is satisfied.
For example, if the Join condition provided was employees.department_id = departments.department_id, the sample query would be:
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
|---|
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 5 | Eve | Engineering |
| 3 | Charlie | Sales |
LEFT JOIN / LEFT OUTER JOIN
Left Join (or Left Outer join) will return columns from both the tables and match records with records from the left table. The result-set will contain null for the rows for which there is no matching row on the right side.
For example, if the Join condition provided was employees.department_id = departments.department_id, the sample query would be:
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
|---|
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 3 | Charlie | Sales |
| 4 | David | NULL |
| 5 | Eve | Engineering |
RIGHT JOIN / RIGHT OUTER JOIN
Right Join (or Right Outer Join) returns matching rows from both tables and preserves all rows from the right table. Rows from the right table that do not have a match in the left table will contain NULL values for the left table columns.
For example, if the join condition is employees.department_id = departments.department_id, the sample query would be:
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
|---|
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 5 | Eve | Engineering |
| 3 | Charlie | Sales |
| NULL | NULL | Marketing |
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 5 | Eve | Engineering |
| 3 | Charlie | Sales |
| NULL | NULL | Marketing |
</div>
### FULL JOIN / FULL OUTER JOIN
Full Outer Join will return columns from both the tables and matching records with records from the left table and records from the right table. The result-set will contain NULL values for the rows for which there is no matching.
For example, if the Join condition provided was `employees.department_id = departments.department_id`, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 3 | Charlie | Sales |
| 4 | David | NULL |
| 5 | Eve | Engineering |
| NULL | NULL | Marketing |
</div>
### CROSS JOIN
Returns the Cartesian product of two datasets. It combines all rows from both tables. Cross Join will not have any Join conditions specified.
For example, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 1 | Alice | Engineering |
| 1 | Alice | Sales |
| 1 | Alice | Marketing |
| 2 | Bob | HR |
| 2 | Bob | Engineering |
| 2 | Bob | Sales |
| 2 | Bob | Marketing |
| 3 | Charlie | HR |
| 3 | Charlie | Engineering |
| 3 | Charlie | Sales |
| 3 | Charlie | Marketing |
| 4 | David | HR |
| 4 | David | Engineering |
| 4 | David | Sales |
| 4 | David | Marketing |
| 5 | Eve | HR |
| 5 | Eve | Engineering |
| 5 | Eve | Sales |
| 5 | Eve | Marketing |
</div>
### NATURAL INNER JOIN
A natural join (or Natural Inner Join) is identical to an explicit Inner Join but it automatically joins columns with the same names in both tables. Natural Join will not have any join conditions specified.
For example, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 5 | Eve | Engineering |
| 3 | Charlie | Sales |
</div>
### NATURAL LEFT OUTER JOIN
A natural Left Outer join (or Natural Left Join) is identical to an explicit Left Outer Join but it automatically joins columns with the same names in both tables. Natural Left Outer Join will not have any join conditions specified.
For example, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 3 | Charlie | Sales |
| 4 | David | NULL |
| 5 | Eve | Engineering |
</div>
### NATURAL RIGHT OUTER JOIN
A natural Left Right join (or Natural Right Join) is identical to an explicit Right Outer Join but it automatically joins columns with the same names in both tables. Natural Right Outer Join will not have any join conditions specified.
For example, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 5 | Eve | Engineering |
| 3 | Charlie | Sales |
| NULL | NULL | Marketing |
</div>
### NATURAL FULL OUTER JOIN
A natural Full Outer join (or Natural Full Join) is identical to an explicit Full Outer Join but it automatically joins columns with the same names in both tables. Natural Full Outer Join will not have any join conditions specified.
For example, the sample query would be:
<div class="table-example">
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_NAME |
| :---------- | :------------ | :-------------- |
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 3 | Charlie | Sales |
| 4 | David | NULL |
| 5 | Eve | Engineering |
| NULL | NULL | Marketing |
</div>
## Similar tools and concepts
The Join gem combines related data from multiple datasets using shared column values.
You may recognize similar behavior from:
- SQL `JOIN` operations
- the Alteryx Join tool
- PySpark `join()`
- Pandas `merge()`
