Join gem for Data Analysis - Prophecy Documentation

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. By default, the Join gem provides a guided interface for choosing a join type, defining match conditions, and selecting output columns. If you need more than two input datasets or a condition that Simple mode can’t represent, you can switch to Advanced mode to edit the underlying SQL configuration directly.

Modes

The Join gem supports two editing modes:

Mode	Description
Simple mode (default)	Build a join using guided controls for the join type, match conditions, and output columns. Supports two input datasets.
Advanced mode	Edit the full join configuration directly. Required for three or more input datasets, or conditions that Simple mode can’t represent.

Simple mode

Simple mode organizes the Join gem into three parts.

Section	Description
Join type	A dropdown at the top of the dialog. See How to choose a join type.
Match rows on	One row per condition, each comparing a column from the first input to a column from the second input.
Output columns	Choose which columns from either input appear in the result, and optionally rename them.

The dialog also shows a Where rows go panel that explains which output port each row of your data lands on for the selected join type. See Output ports in Simple mode.

Match rows on

Each row compares one column from the first input to one column from the second input using an operator:

Operator	Meaning
`=`	equals
`!=`	not equals
`<`	less than
`<=`	less than or equal
`>`	greater than
`>=`	greater than or equal

Multiple rows are combined with AND. If your join needs OR logic, a function call, or any comparison Simple mode can’t show as a row, the gem falls back to Advanced mode — see Complex joins.

Output columns

Pick any column from either input to include in the result. If you don’t rename a column, Prophecy uses the column name as the output name automatically. Semi and anti joins only offer columns from the first input. A semi join keeps rows from the first input that have a match in the second, and an anti join keeps rows from the first input that don’t. Neither one adds columns from the second input to the result.

How to choose a join type

Join type	When to use it
Inner Join	Keep only matching rows.
Left Join	Keep all rows from the first dataset.
Right Join	Keep all rows from the second dataset.
Full Outer Join	Keep all rows from both datasets.
Cross Join	Combine every row from both datasets.
Semi Join	Keep rows from the first dataset that have a match in the second, without adding any of its columns.
Anti Join	Keep rows from the first dataset that have no match in the second.

Simple mode’s join-type dropdown offers Inner, Left Outer, and Right Outer Join. Full Outer, Cross, Natural, and Semi/Anti joins are only available in Advanced mode.

Complex joins

Simple mode can represent a join when all of the following are true:

Exactly two input datasets are connected.
There’s exactly one join condition connecting them.
The join type is Inner, Left Outer, or Right Outer (or another type already configured in Advanced mode that Simple mode recognizes).
The condition is one or more column comparisons combined with AND (no OR, no functions).
Every output column is a plain column pick, not a calculated expression like concat(a, b).

If any of these aren’t true, Simple mode shows a message that the configuration is too complex to edit there. From this message you have two options:

Switch to Advanced Mode lets you edit the existing configuration directly.
Reset and use Default Mode clears the match conditions and output columns so you can start over with a simple inner join.

Reset and use Default Mode permanently removes the existing match conditions and output columns.

If the gem has more than two input datasets, only Switch to Advanced Mode is available — reducing the join to two inputs isn’t something Reset can do for you.

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.

Adding a third input, or opening a gem that already has one, moves the Join gem to Advanced mode — Simple mode only represents two-input joins.

Output ports in Simple mode

In Simple mode the Join gem always has three output ports, in this order:

Port	Contents
out0	Rows from the first input that didn’t make it into the joined result.
out1	The joined (matched) rows.
out2	Rows from the second input that didn’t make it into the joined result.

Which reject ports actually carry rows depends on the join type:

Join type	out0 (left reject)	out1 (matched)	out2 (right reject)
Inner	rows	rows	rows
Left Outer	empty	rows	rows
Right Outer	rows	rows	empty

A Left Outer join, for example, keeps every row from the first input in the matched output, so out0 never has anything to emit — but a right-side row with no match is dropped from the joined result, and shows up on out2 instead of disappearing silently. Connect a reject port downstream if you want to inspect or route unmatched rows rather than losing them.

Output port in Advanced mode

Port	Description
out	A single table that results from the join operation(s).

Advanced mode configuration

Advanced mode exposes the underlying join configuration directly, instead of the guided Simple mode cards.

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

In Simple mode, check the reject ports (out0 / out2) — rows that don’t appear in the matched output often show up there instead of being silently dropped. See Output ports in Simple mode.

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

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;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_NAME
1	Alice	HR
2	Bob	Engineering
3	Charlie	Sales
4	David	NULL
5	Eve	Engineering
NULL	NULL	Marketing

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;

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

LEFT SEMI JOIN

A semi join returns rows from the left table that have at least one matching row in the right table — but only the left table’s columns come through. Unlike an Inner Join, a semi join never duplicates a left row when it has more than one match on the right. 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, e.department_id
 FROM employees e
 LEFT SEMI JOIN departments d
 ON e.department_id = d.department_id;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
1	Alice	10
2	Bob	20
3	Charlie	30
5	Eve	20

LEFT ANTI JOIN

An anti join returns rows from the left table that have no matching row in the right table — the inverse of a semi join. Only the left table’s columns come through. 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, e.department_id
 FROM employees e
 LEFT ANTI JOIN departments d
 ON e.department_id = d.department_id;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
4	David	NULL

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;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_NAME
1	Alice	HR
2	Bob	Engineering
5	Eve	Engineering
3	Charlie	Sales

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;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_NAME
1	Alice	HR
2	Bob	Engineering
3	Charlie	Sales
4	David	NULL
5	Eve	Engineering

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;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_NAME
1	Alice	HR
2	Bob	Engineering
5	Eve	Engineering
3	Charlie	Sales
NULL	NULL	Marketing

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;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_NAME
1	Alice	HR
2	Bob	Engineering
3	Charlie	Sales
4	David	NULL
5	Eve	Engineering
NULL	NULL	Marketing

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

​Modes

​Simple mode

​Match rows on

​Output columns

​How to choose a join type

​Complex joins

​Common join examples

​Input and Output

​Output ports in Simple mode

​Output port in Advanced mode

​Advanced mode configuration

​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

​FULL JOIN / FULL OUTER JOIN

​CROSS JOIN

​LEFT SEMI JOIN

​LEFT ANTI JOIN

​NATURAL INNER JOIN

​NATURAL LEFT OUTER JOIN

​NATURAL RIGHT OUTER JOIN

​NATURAL FULL OUTER JOIN

​Similar tools and concepts

Overview

Modes

Simple mode

Match rows on

Output columns

How to choose a join type

Complex joins

Common join examples

Input and Output

Output ports in Simple mode

Output port in Advanced mode

Advanced mode configuration

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

FULL JOIN / FULL OUTER JOIN

CROSS JOIN

LEFT SEMI JOIN

LEFT ANTI JOIN

NATURAL INNER JOIN

NATURAL LEFT OUTER JOIN

NATURAL RIGHT OUTER JOIN

NATURAL FULL OUTER JOIN

Similar tools and concepts