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

> Join one or more DataFrames on conditions

<Panel>
  <Info>
    Dependencies:

    * ProphecySparkBasicsPython 0.0.1+
    * ProphecySparkBasicsScala 0.0.1+
  </Info>

  <Info>
    Cluster requirements:

    * UC dedicated clusters 14.3+ supported
    * UC standard clusters 14.3+ supported
    * Livy clusters 3.0.1+ supported
  </Info>
</Panel>

Joins 2 or more DataFrames based on the given configuration.

## Parameters

| Parameter                        | Description                                                                                                                                                                                                     | Required |
| :------------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- |
| DataFrame 1                      | First input DataFrame                                                                                                                                                                                           | True     |
| DataFrame 2                      | Second input DataFrame                                                                                                                                                                                          | True     |
| DataFrame N                      | Nth input DataFrame                                                                                                                                                                                             | False    |
| Join Condition (Conditions tab)  | The join condition specifies how the rows will be combined.                                                                                                                                                     | True     |
| Type (Conditions tab)            | The type of JOIN `(Inner, Full Outer, Left , Right , Left Semi, Left Anti)`                                                                                                                                     | True     |
| Where Clause (Conditions tab)    | `Filter` applied after the Join operation                                                                                                                                                                       | False    |
| Target column (Expressions)      | Output column name                                                                                                                                                                                              | False    |
| Expression (Expressions)         | Expression to compute target column. If no expression is given, then all columns from all DataFrames would reflect in output.                                                                                   | False    |
| Hint Type (Advanced)             | The type of Join Hint (`Broadcast`, `Merge`, `Shuffle Hash`, `Shuffle Replicate NL` or `None`). To read more about join hints [click here](https://developpaper.com/analysis-of-five-join-strategies-of-spark/) | False    |
| Propagate All Columns (Advanced) | If `true`, all columns from that DataFrame would be propagated to output DataFrame. Equivalent to selecting `df.*` for the selected DataFrame.                                                                  | False    |

## Adding a new input

1. Click on the **+** icon to add a new input.
2. Then add your condition expression for the newly added input.

<img src="https://mintcdn.com/prophecy-62973bd0/J5UDJ9ATlxoXhTI3/data-engineering/gems/join-split/img/add_new_input.png?fit=max&auto=format&n=J5UDJ9ATlxoXhTI3&q=85&s=27dc717920ee29f720dd63e2c72cb84c" alt="Example usage of Join - Add new input to join gem" width="1616" height="802" data-path="data-engineering/gems/join-split/img/add_new_input.png" />

## Examples

### Example 1 - Join with three DataFrame inputs

<img src="https://mintcdn.com/prophecy-62973bd0/J5UDJ9ATlxoXhTI3/data-engineering/gems/join-split/img/join_without_hints.png?fit=max&auto=format&n=J5UDJ9ATlxoXhTI3&q=85&s=6c38fe574279d0954c48eaeee643e28c" alt="Example usage of Join - Join three DataFrame inputs" width="1618" height="806" data-path="data-engineering/gems/join-split/img/join_without_hints.png" />

<CodeGroup>
  ```python example.py theme={null}
  def Join_1(spark: SparkSession, in0: DataFrame) -> DataFrame:
   return in0\
   .alias("in0")\
   .join(in1.alias("in1"), (col("in0.customer_id") == col("in1.customer_id")), "inner")\
   .join(in2.alias("in2"), (col("in1.customer_id") == col("in2.customer_id")), "inner")
  ```

  ```scala example.scala theme={null}
  object Join_1 {
   def apply(spark: SparkSession, in0: DataFrame, in1: DataFrame, in2: DataFrame): DataFrame =
   in0
   .as("in0")
   .join(in1.as("in1"), col("in0.customer_id") === col("in1.customer_id"), "inner")
   .join(in2.as("in2"), col("in1.customer_id") === col("in2.customer_id"), "inner")
  }
  ```
</CodeGroup>

### Example 2 - Join with Hints

Join hints allow users to suggest the join strategy that Spark should use. For a quick overview, see Spark's Join Hints [documentation](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html#join-hints).

<img src="https://mintcdn.com/prophecy-62973bd0/J5UDJ9ATlxoXhTI3/data-engineering/gems/join-split/img/join_with_hints.png?fit=max&auto=format&n=J5UDJ9ATlxoXhTI3&q=85&s=8ca52e159bcb82ff66ea04825eb0d43e" alt="Example usage of Join - Join with hints" width="1644" height="668" data-path="data-engineering/gems/join-split/img/join_with_hints.png" />

<CodeGroup>
  ```python example.py theme={null}
  def Join_1(spark: SparkSession, in0: DataFrame, in1: DataFrame, in2: DataFrame) -> DataFrame:
   df1 = in1.hint("merge")

   return in0\
   .alias("in0")\
   .hint("broadcast")\
   .join(df1.alias("in1"), col("in0.customer_id") == col("in1.customer_id"), "inner")\
   .join(in2.alias("in2"), col("in0.customer_id") == col("in1.customer_id"), "inner")
  ```

  ```scala example.scala theme={null}
  object Join_1 {
   def apply(spark: SparkSession, in0: DataFrame, in1: DataFrame, in2: DataFrame): DataFrame =
   in0
   .as("in0")
   .hint("broadcast")
   .join(in1.as("in1").hint("merge"), col("in0.customer_id") === col("in1.customer_id"), "inner")
   .join(in2.as("in2"), col("in1.customer_id") === col("in2.customer_id"), "inner")
  }
  ```
</CodeGroup>

### Example 3 - Join with Propagate Columns

```
import App from '@site/src/components/slider';

export const ImageData = [
 {
 "image":"/img/join/join-eg3-conditions.png",
 "description":<h3 style="padding:10px">Step 1 - Specify join condition</h3>,
 },
 {
 "image":"/img/join/join-eg3-expressions.png",
 "description":<h3 style="padding:10px">Step 2 - Choose required columns from dataframe</h3>,
 },
 {
 "image":"/img/join/join-eg3-advanced.png",
 "description":<h3 style="padding:10px">Step 3 - Select Propagate all columns from in0</h3>,
 },
 {
 "image":"/img/join/join-eg3-output.png",
 "description":<h3 style="padding:10px">Output - Output with all columns from in0 and selected columns from in1</h3>
 },
];

<App ImageData={ImageData}></App>
```

<CodeGroup>
  ```python example.py theme={null}
  def Join_1(spark: SparkSession, in0: DataFrame, in1: DataFrame, ) -> DataFrame:
   return in0\
   .alias("in0")\
   .join(in1.alias("in1"), (col("in0.customer_id") == col("in1.customer_id")), "inner")\
   .select(*[col("in1.email").alias("email"), col("in1.phone").alias("phone")], col("in0.*"))
  ```

  ```scala example.scala theme={null}
  object Join_1 {

   def apply(spark: SparkSession, in0: DataFrame, in1: DataFrame): DataFrame =
   in0
   .as("in0")
   .join(in1.as("in1"), col("in0.customer_id") === col("in1.customer_id"), "inner")
   .select(col("in1.phone").as("phone"), col("in1.email").as("email"), col("in0.*"))

  }
  ```
</CodeGroup>

## Types of Join

Suppose there are 2 tables TableA and TableB with only 2 columns (Ref, Data) and following contents:

### Table A

| Ref | Data      |
| :-- | :-------- |
| 1   | Data\_A11 |
| 1   | Data\_A12 |
| 1   | Data\_A13 |
| 2   | Data\_A21 |
| 3   | Data\_A31 |

### Table B

| Ref | Data      |
| :-- | :-------- |
| 1   | Data\_B11 |
| 2   | Data\_B21 |
| 2   | Data\_B22 |
| 2   | Data\_B23 |
| 4   | Data\_B41 |

### INNER JOIN

Inner Join on column Ref will return columns from both the tables and only the matching records as long as the condition is satisfied:

| Ref | Data      | Ref | Data      |
| :-- | :-------- | :-- | :-------- |
| 1   | Data\_A11 | 1   | Data\_B11 |
| 1   | Data\_A12 | 1   | Data\_B11 |
| 1   | Data\_A13 | 1   | Data\_B11 |
| 2   | Data\_A21 | 2   | Data\_B21 |
| 2   | Data\_A21 | 2   | Data\_B22 |
| 2   | Data\_A21 | 2   | Data\_B23 |

### LEFT JOIN

Left Join (or Left Outer join) on column Ref 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.

| Ref | Data      | Ref  | Data      |
| :-- | :-------- | :--- | :-------- |
| 1   | Data\_A11 | 1    | Data\_B11 |
| 1   | Data\_A12 | 1    | Data\_B11 |
| 1   | Data\_A13 | 1    | Data\_B11 |
| 2   | Data\_A21 | 2    | Data\_B21 |
| 2   | Data\_A21 | 2    | Data\_B22 |
| 2   | Data\_A21 | 2    | Data\_B23 |
| 3   | Data\_A31 | NULL | NULL      |

### RIGHT JOIN

Right Join (or Right Outer join) on column Ref will return columns from both the tables and match records with records from the right table. The result-set will contain null for the rows for which there is no matching row on the left side.

| Ref  | Data      | Ref | Data      |
| :--- | :-------- | :-- | :-------- |
| 1    | Data\_A11 | 1   | Data\_B11 |
| 1    | Data\_A12 | 1   | Data\_B11 |
| 1    | Data\_A13 | 1   | Data\_B11 |
| 2    | Data\_A21 | 2   | Data\_B21 |
| 2    | Data\_A21 | 2   | Data\_B22 |
| 2    | Data\_A21 | 2   | Data\_B23 |
| NULL | NULL      | 4   | Data\_B41 |

### FULL OUTER JOIN

Full Outer Join on column Ref 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.

| Ref  | Data      | Ref  | Data      |
| :--- | :-------- | :--- | :-------- |
| 1    | Data\_A11 | 1    | Data\_B11 |
| 1    | Data\_A12 | 1    | Data\_B11 |
| 1    | Data\_A13 | 1    | Data\_B11 |
| 2    | Data\_A21 | 2    | Data\_B21 |
| 2    | Data\_A21 | 2    | Data\_B22 |
| 2    | Data\_A21 | 2    | Data\_B23 |
| 3    | Data\_A31 | NULL | NULL      |
| NULL | NULL      | 4    | Data\_B41 |

### LEFT SEMI JOIN

Left Semi Join on column Ref will return columns only from left table and matching records only from left table.

| Ref | Data      |
| :-- | :-------- |
| 1   | Data\_B11 |
| 1   | Data\_B21 |
| 1   | Data\_B22 |
| 2   | Data\_B23 |
| 3   | Data\_B41 |

### LEFT ANTI JOIN

Left anti join on column Ref will return columns from the left for non-matched records :

| Ref | Data      | Ref  | Data |
| :-- | :-------- | :--- | :--- |
| 3   | Data\_A31 | NULL | NULL |