PySpark Join Types – Join Two DataFrames

In this article, we are going to see how to join two dataframes in Pyspark using Python. Join is used to combine two or more dataframes based on columns in the dataframe.

Syntax: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”type”) 

where,

1. dataframe1 is the first dataframe
2. dataframe2 is the second dataframe
3. column_name is the column which are matching in both the dataframes
4. type is the join type we have to join

Create the first dataframe for demonstration:

Output:

Create second dataframe for demonstration:

Output:

Inner join

This will join the two PySpark dataframes on key columns, which are common in both dataframes.

Syntax: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”inner”)

Example:

Output:

Full Outer Join

This join joins the two dataframes with all matching and non-matching rows, we can perform this join in three ways

Syntax:

• outer: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”outer”)
• full: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”full”)
• fullouter: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”fullouter”)

Example 1: Using outer keyword

In this example, we are going to perform outer join based on the ID column in both dataframes.

Output:

Example 2: Using full keyword

In this example, we are going to perform outer join using full keyword based on ID column in both dataframes.

Output:

Example 3: Using fullouter keyword

In this example, we are going to perform outer join using full outer based on ID column in both dataframes.

Output:

Left Join

Here this join joins the dataframe by returning all rows from the first dataframe and only matched rows from the second dataframe with respect to the first dataframe. We can perform this type of join using left and leftouter.

Syntax:

• left: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”left”)
• leftouter: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”leftouter”)

Example 1: Perform left join

In this example, we are going to perform left join using the left keyword based on the ID column in both dataframes.

Output:

Example 2: Perform leftouter join

In this example, we are going to perform left join  using leftouter keyword based on the ID column in both dataframes

Output

Right Join

Here this join joins the dataframe by returning all rows from the second dataframe and only matched rows from the first dataframe with respect to the second dataframe. We can perform this type of join using right and rightouter.

Syntax:

• right: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”right”)
• rightouter: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”rightouter”)

Example 1: Perform right join

In this example, we are going to perform right join using the right keyword based on ID column in both dataframes.

Output:

Example 2: Perform rightouter join

In this example, we are going to perform the right join using rightouter keyword based on the ID column in both dataframes.

Output:

Leftsemi join

This join will all rows from the first dataframe and return only matched rows from the second dataframe

Syntax: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”leftsemi”)

Example: In this example, we are going to perform leftsemi join using leftsemi keyword based on the ID column in both dataframes.

Output:

LeftAnti join

This  join returns only columns from the first dataframe for non-matched records of the second dataframe

Syntax: dataframe1.join(dataframe2,dataframe1.column_name ==  dataframe2.column_name,”leftanti”)

Example: In this example, we are going to perform leftanti join using leftanti keyword based on the ID column in both dataframes.

Output:

SQL Expression

We can perform all types of the above joins using an SQL expression, we have to mention the type of join in this expression. To do this, we have to create a temporary view.

Syntax: dataframe.createOrReplaceTempView(“name”)

where

• dataframe is the input dataframe
• name is the view name

Now we can perform join on these views using spark.sql().

Syntax: spark.sql(“select * from dataframe1, dataframe2 where dataframe1.column_name == dataframe2.column_name “)

where,

• dataframe1 is the first view dataframe
• dataframe2 is the second view dataframe
• column_name is the column to be joined

Example 1: In this example, we are going to join two dataframes based on the ID column.

Output:

We can also perform the above joins using this SQL expression:

Syntax: spark.sql(“select * from dataframe1 JOIN_TYPE dataframe2 ON dataframe1.column_name == dataframe2.column_name “)

where, JOIN_TYPE refers to above all types of joins

Example 2: Perform inner join on ID column using expression

Output: