4.PySpark Join Operations

---

In PySpark, the join operation is used to combine two DataFrames based on a condition. The join operation can be performed in several ways, such as inner join, left join, right join, outer join, semi join, and anti join.

Syntax

DataFrame.join(other, on=None, how=None)

• other: The DataFrame to join with.
• on: The column(s) to join on. This can be a string representing a single column name, a list of column names, or a condition.
• how: The type of join to perform. Default is inner.

Join Types

• inner: Inner join.
• left or left_outer: Left outer join.
• right or right_outer: Right outer join.
• outer or full or full_outer: Full outer join.
• left_semi: Left semi join.
• left_anti: Left anti join.

Spark SQL Join Types- Syntax examples, Comparision

Examples

data1 = [
    ("Alice", 34, "HR"),
    ("Bob", 45, "IT"),
    ("Catherine", 29, "HR"),
    ("David", 36, "IT")
]
columns1 = ["Name", "Age", "Department"]

data2 = [
    ("HR", 3000),
    ("IT", 4000),
    ("Sales", 2800)
]
columns2 = ["Department", "Salary"]

df1 = spark.createDataFrame(data1, schema=columns1)
df2 = spark.createDataFrame(data2, schema=columns2)

df1.show()
df2.show()

1. Inner Join

inner_join_df = df1.join(df2, on="Department", how="inner")
inner_join_df.show()

Output:

+---------+---+----------+------+
|     Name|Age|Department|Salary|
+---------+---+----------+------+
|    Alice| 34|        HR|  3000|
|Catherine| 29|        HR|  3000|
|      Bob| 45|        IT|  4000|
|    David| 36|        IT|  4000|
+---------+---+----------+------+

2. Left Outer Join

left_join_df = df1.join(df2, on="Department", how="left")
left_join_df.show()

Output:

+---------+---+----------+------+
|     Name|Age|Department|Salary|
+---------+---+----------+------+
|    Alice| 34|        HR|  3000|
|Catherine| 29|        HR|  3000|
|      Bob| 45|        IT|  4000|
|    David| 36|        IT|  4000|
+---------+---+----------+------+

3. Right Outer Join

right_join_df = df1.join(df2, on="Department", how="right")
right_join_df.show()

Output:

+---------+----+----------+------+
|     Name| Age|Department|Salary|
+---------+----+----------+------+
|    Alice|  34|        HR|  3000|
|Catherine|  29|        HR|  3000|
|      Bob|  45|        IT|  4000|
|    David|  36|        IT|  4000|
|     null|null|     Sales|  2800|
+---------+----+----------+------+

4. Full Outer Join

outer_join_df = df1.join(df2, on="Department", how="outer")
outer_join_df.show()

Output:

+---------+----+----------+------+
|     Name| Age|Department|Salary|
+---------+----+----------+------+
|    Alice|  34|        HR|  3000|
|Catherine|  29|        HR|  3000|
|      Bob|  45|        IT|  4000|
|    David|  36|        IT|  4000|
|     null|null|     Sales|  2800|
+---------+----+----------+------+

5. Left Semi Join

semi_join_df = df1.join(df2, on="Department", how="left_semi")
semi_join_df.show()

Output:

+---------+---+----------+
|     Name|Age|Department|
+---------+---+----------+
|    Alice| 34|        HR|
|Catherine| 29|        HR|
|      Bob| 45|        IT|
|    David| 36|        IT|
+---------+---+----------+

6. Left Anti Join

anti_join_df = df1.join(df2, on="Department", how="left_anti")
anti_join_df.show()

Output:

+----+---+----------+
|Name|Age|Department|
+----+---+----------+
+----+---+----------+

7. Join on Multiple Columns

data3 = [
    ("Alice", 34, "HR", 3000),
    ("Bob", 45, "IT", 4000),
    ("Catherine", 29, "HR", 5000),
    ("David", 36, "IT", 2500),
    ("Eve", 28, "Sales", 2800)
]
columns3 = ["Name", "Age", "Department", "Salary"]

data4 = [
    ("HR", 34, 3000, "Manager"),
    ("IT", 45, 4000, "Developer"),
    ("Sales", 28, 2800, "Salesman")
]
columns4 = ["Department", "Age", "Salary", "Role"]

df3 = spark.createDataFrame(data3, schema=columns3)
df4 = spark.createDataFrame(data4, schema=columns4)

multi_col_join_df = df3.join(df4, on=["Department", "Age", "Salary"], how="inner")
multi_col_join_df.show()

Output:

+-----+---+----------+------+--------+
| Name|Age|Department|Salary|    Role|
+-----+---+----------+------+--------+
|Alice| 34|        HR|  3000| Manager|
|  Bob| 45|        IT|  4000|Developer|
+-----+---+----------+------+--------+

---

Head to Next

Pages: 1 2 3 4 5 6 7 8 9 10 11 12