# Joins — Apache Spark: Big Data Processing with DataFrames

Source: https://www.geekswithgeeks.com/en/spark/df-joins

> Join DataFrames, choose the join type and avoid accidental row multiplication.

## Match rows on a key

`df.join(other, "customer", "left")` combines rows that share a key. Join types: **inner** (only matches), **left/right/full outer** (keep unmatched rows from one or both sides, filling the other side with null), **left semi** (rows of the left that have a match, without adding columns) and **left anti** (rows of the left with no match). Joins are the most expensive common operation because data may need to be **shuffled** so matching keys meet. Two traps: joining on a **non-unique key** multiplies rows (many-to-many), and joining on a column with **nulls** never matches them. Check row counts before and after.

## A left join, run

I ran this on Apache Spark 4.0.0 (PySpark, local mode, in the official Docker image). Order 6 (Kiran) has no matching tier, so the left join keeps the row and shows `NULL` for `tier`.

```python
cust = spark.createDataFrame([("asha", "gold"), ("ravi", "silver"), ("meera", "bronze")], ["customer", "tier"])
orders.join(cust, "customer", "left").select("order_id", "customer", "tier").orderBy("order_id").show()
```

Output:

```
+--------+--------+------+
|order_id|customer|  tier|
+--------+--------+------+
|       1|    asha|  gold|
|       2|    ravi|silver|
|       3|    asha|  gold|
|       4|   meera|bronze|
|       5|    ravi|silver|
|       6|   kiran|  NULL|
+--------+--------+------+
```

## Join on explicit keys

Count rows before and after a join, and use `df1.join(df2, ["a", "b"], "inner")` with explicit column lists. Unexpected growth usually means the "unique" key was not unique.

**Quiz:** Which join keeps every row of the left DataFrame, with nulls where there is no match?

- [x] left outer join
- [ ] inner join
- [ ] left anti join
- [ ] cross join without keys

*Answer:* left outer join. A left outer join preserves unmatched left rows and fills right columns with null.