Join gem for Data Analysis

This gem runs in .

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

Input and Output

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).

To add additional input ports, click + 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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;

<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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e CROSS JOIN departments d;

<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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e CROSS JOIN departments d;

<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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e NATURAL LEFT OUTER JOIN departments d;

<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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e NATURAL RIGHT OUTER JOIN departments d;

<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:

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e NATURAL FULL OUTER JOIN departments d;

<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()`

Overview

Sources and targets

Data processing

Reporting

Advanced

Overview

How to choose a join type

Common join examples

Input and Output

Parameters

Join conditions

Expressions

Example

Common issues

Duplicate rows after joining

Missing records in the output

Null values in joined columns

Ambiguous column names

Join types

Employees

Departments

INNER JOIN

LEFT JOIN / LEFT OUTER JOIN

RIGHT JOIN / RIGHT OUTER JOIN

​Overview

​How to choose a join type

​Common join examples

​Input and Output

​Parameters

​Join conditions

​Expressions

​Example

​Common issues

​Duplicate rows after joining

​Missing records in the output

​Null values in joined columns

​Ambiguous column names

​Join types

​Employees

​Departments

​INNER JOIN

​LEFT JOIN / LEFT OUTER JOIN

​RIGHT JOIN / RIGHT OUTER JOIN

Overview

How to choose a join type

Common join examples

Input and Output

Parameters

Join conditions

Expressions

Example

Common issues

Duplicate rows after joining

Missing records in the output

Null values in joined columns

Ambiguous column names

Join types

Employees

Departments

INNER JOIN

LEFT JOIN / LEFT OUTER JOIN

RIGHT JOIN / RIGHT OUTER JOIN