> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Join gem for Data Analysis

> Join two or more datasets

export const gemName_0 = "Join"

export const execution_engine_0 = "the SQL warehouse"

<Info>This gem runs in {execution_engine_0}.</Info>

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

<Tip>
  The {gemName_0} gem has a corresponding interactive gem example. See [Interactive gem
  examples](/data-analysis/gems/gems#interactive-gem-examples) to learn how to run sample pipelines
  for this and other gems.
</Tip>

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

<Info>
  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.
</Info>

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

1. Connect **orders** to **in0** and **customers** to **in1**.
2. Choose **Inner Join** as the join type.
3. If using a visual expression, use the following join condition: **in0.CustomerID *equals* in1.customer\_id**
4. If using the code expression, use the following SQL join condition: **in0.CustomerID = in1.customer\_id**
5. Leave the **Expressions** tile empty.
6. 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

<div class="table-example">
  | EMPLOYEE\_ID | EMPLOYEE\_NAME | DEPARTMENT\_ID |
  | :----------- | :------------- | :------------- |
  | 1            | Alice          | 10             |
  | 2            | Bob            | 20             |
  | 3            | Charlie        | 30             |
  | 4            | David          | NULL           |
  | 5            | Eve            | 20             |
</div>

### Departments

<div class="table-example">
  | DEPARTMENT\_ID | DEPARTMENT\_NAME |
  | :------------- | :--------------- |
  | 10             | HR               |
  | 20             | Engineering      |
  | 30             | Sales            |
  | 40             | Marketing        |
</div>

### 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;
```

<div class="table-example">
  | EMPLOYEE\_ID | EMPLOYEE\_NAME | DEPARTMENT\_NAME |
  | :----------- | :------------- | :--------------- |
  | 1            | Alice          | HR               |
  | 2            | Bob            | Engineering      |
  | 5            | Eve            | Engineering      |
  | 3            | Charlie        | Sales            |
</div>

### 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;
```

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

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

```sql theme={null}
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;
```

<div className="table-example">
  | EMPLOYEE\_ID | EMPLOYEE\_NAME | DEPARTMENT\_NAME |
  | :----------- | :------------- | :--------------- |
  | 1            | Alice          | HR               |
  | 2            | Bob            | Engineering      |
  | 5            | Eve            | Engineering      |
  | 3            | Charlie        | Sales            |
  | NULL         | NULL           | Marketing        |
</div>

```

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