# Understanding Join Types in Spark

> Growing Tips (Apache Iceberg & Spark 101), part 11 of 16.
> Inner, left, full, semi, and anti joins. Choosing the right graft when joining two tables together.
> Source: https://oleander.dev/grow/understanding-join-types-in-spark

In the previous parts, we used Spark SQL and the DataFrame API to solve practical data problems. We summarized clickstream events, built product funnels, and measured cart abandonment.

Along the way, we also used a left join to connect cart sessions with purchase sessions.

That gave us a practical introduction to joins, but joins are important enough that it is worth pausing for a moment and looking at them more carefully.

In this part, we will step away from the larger clickstream jobs and focus on seven join types:

1. inner join
2. left join
3. right join
4. full join
5. cross join
6. left semi join
7. left anti join

We will use the same two small retail datasets for every example. That way, we can focus on how the join changes the result instead of learning a new business scenario each time.

We also do not need to create any Iceberg tables for this part. The datasets are small, temporary, and only exist to help us understand the concepts.

## Preparing the Example Data

Let’s imagine that the Oleander retail website has two products:

| `product_id` | `product_name` | `category`  |
| ------------ | -------------- | ----------- |
| `P100`       | Trail Mug      | `drinkware` |
| `P200`       | Logo T-Shirt   | `apparel`   |

It also has two category promotions:

| `promotion_id` | `promotion_name`  | `category`  |
| -------------- | ----------------- | ----------- |
| `D10`          | Drinkware 10% Off | `drinkware` |
| `D20`          | Footwear 20% Off  | `footwear`  |

The `drinkware` category appears in both datasets. The `apparel` category appears only in the product dataset, and `footwear` appears only in the promotion dataset.

This gives us everything we need:

- one matching category
- one category that exists only on the left
- one category that exists only on the right

Start the PySpark shell and create the two DataFrames:

```python
products = spark.createDataFrame(
    [
        ("P100", "Trail Mug", "drinkware"),
        ("P200", "Logo T-Shirt", "apparel"),
    ],
    ["product_id", "product_name", "category"],
)

category_promotions = spark.createDataFrame(
    [
        ("D10", "Drinkware 10% Off", "drinkware"),
        ("D20", "Footwear 20% Off", "footwear"),
    ],
    ["promotion_id", "promotion_name", "category"],
)
```

Register them as temporary views so that we can also query them with Spark SQL:

```python
products.createOrReplaceTempView("products")
category_promotions.createOrReplaceTempView("category_promotions")
```

A temporary view gives a DataFrame a name that Spark SQL can query. It does not copy the data or create a permanent table. The view exists only for the current Spark session and disappears when that session ends.

Throughout this part, `products` will be the **left** side and `category_promotions` will be the **right** side.

Those words describe where the datasets appear in a particular join. They are not permanent properties of the datasets. If we reverse their positions in a query, what we call left and right reverses too.

## The Join Key

Except for the cross join, every example will match rows using `category`.

In SQL, we will write:

```sql
USING (category)
```

`USING` tells Spark that both sides have a column named `category` and that this column is the join key. It also gives us one `category` column in the result instead of two separate columns with the same name.

We could express the same match with `ON`:

```sql
ON products.category = category_promotions.category
```

`ON` accepts a complete matching condition, so it is useful when the key columns have different names or when a join needs more than a simple same-name equality. Unlike `USING`, it keeps both key columns available. We would then qualify them as `products.category` and `category_promotions.category` to make clear which one we mean.

For these examples, both datasets use the same key name and we want only one `category` column in the result, so `USING (category)` keeps the queries pleasantly small.

The equivalent DataFrame call looks like this:

```python
products.join(category_promotions, "category", "inner")
```

The second argument is the join key. The third argument is the join type.

We will also sort every result before showing it. Spark does not guarantee row order unless we explicitly request one, and changing row order between examples would make the comparison unnecessarily exciting.

## Inner Join

An **inner join** keeps only rows whose join key exists on both sides.

In our example, `drinkware` is the only category shared by the product and promotion datasets. That means the inner join returns one row.

Here is the Spark SQL version:

```python
spark.sql("""
    SELECT
      category,
      product_id,
      product_name,
      promotion_id,
      promotion_name
    FROM products
    INNER JOIN category_promotions USING (category)
    ORDER BY category
""").show(truncate=False)
```

And here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "inner")
    .select(
        "category",
        "product_id",
        "product_name",
        "promotion_id",
        "promotion_name",
    )
    .orderBy("category")
    .show(truncate=False)
)
```

Both produce:

```text
+---------+----------+------------+------------+-----------------+
|category |product_id|product_name|promotion_id|promotion_name   |
+---------+----------+------------+------------+-----------------+
|drinkware|P100      |Trail Mug   |D10         |Drinkware 10% Off|
+---------+----------+------------+------------+-----------------+
```

The Logo T-Shirt is removed because `apparel` has no matching promotion. The footwear promotion is also removed because there is no footwear product.

A simple mental model is:

**An inner join keeps only the matches.**

## Left Join

A **left join** keeps every row from the left side.

When a left row has a match on the right, Spark combines them. When it does not have a match, Spark still keeps the left row and fills the right-side columns with `NULL`.

Here is the SQL version:

```python
spark.sql("""
    SELECT
      category,
      product_id,
      product_name,
      promotion_id,
      promotion_name
    FROM products
    LEFT JOIN category_promotions USING (category)
    ORDER BY category
""").show(truncate=False)
```

`LEFT JOIN` and `LEFT OUTER JOIN` mean the same thing. The word `OUTER` is optional.

Here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "left")
    .select(
        "category",
        "product_id",
        "product_name",
        "promotion_id",
        "promotion_name",
    )
    .orderBy("category")
    .show(truncate=False)
)
```

Both produce:

```text
+---------+----------+------------+------------+-----------------+
|category |product_id|product_name|promotion_id|promotion_name   |
+---------+----------+------------+------------+-----------------+
|apparel  |P200      |Logo T-Shirt|NULL        |NULL             |
|drinkware|P100      |Trail Mug   |D10         |Drinkware 10% Off|
+---------+----------+------------+------------+-----------------+
```

Both products remain because `products` is on the left. The Logo T-Shirt has no applicable promotion, so its promotion columns are `NULL`.

This is the same behavior we used in Part 10. Cart sessions were on the left so that carts without matching purchases stayed in the result.

## Right Join

A **right join** is the mirror image of a left join. It keeps every row from the right side.

Here is the SQL version:

```python
spark.sql("""
    SELECT
      category,
      product_id,
      product_name,
      promotion_id,
      promotion_name
    FROM products
    RIGHT JOIN category_promotions USING (category)
    ORDER BY category
""").show(truncate=False)
```

`RIGHT JOIN` and `RIGHT OUTER JOIN` mean the same thing.

Here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "right")
    .select(
        "category",
        "product_id",
        "product_name",
        "promotion_id",
        "promotion_name",
    )
    .orderBy("category")
    .show(truncate=False)
)
```

Both produce:

```text
+---------+----------+------------+------------+-----------------+
|category |product_id|product_name|promotion_id|promotion_name   |
+---------+----------+------------+------------+-----------------+
|drinkware|P100      |Trail Mug   |D10         |Drinkware 10% Off|
|footwear |NULL      |NULL        |D20         |Footwear 20% Off |
+---------+----------+------------+------------+-----------------+
```

Both promotions remain because `category_promotions` is on the right. The footwear promotion has no matching product, so its product columns are `NULL`.

In practice, you can usually rewrite a right join as a left join by reversing the datasets:

```sql
category_promotions LEFT JOIN products USING (category)
```

Some teams prefer that style because they find it easier to read everything as a left join. But right joins are still useful to understand, especially when reading code written by other people.

## Full Join

A **full join** keeps every row from both sides.

Matched rows are combined. Unmatched left rows receive `NULL` for the right-side columns, and unmatched right rows receive `NULL` for the left-side columns.

Here is the SQL version:

```python
spark.sql("""
    SELECT
      category,
      product_id,
      product_name,
      promotion_id,
      promotion_name
    FROM products
    FULL JOIN category_promotions USING (category)
    ORDER BY category
""").show(truncate=False)
```

`FULL JOIN` and `FULL OUTER JOIN` mean the same thing.

Here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "full")
    .select(
        "category",
        "product_id",
        "product_name",
        "promotion_id",
        "promotion_name",
    )
    .orderBy("category")
    .show(truncate=False)
)
```

Both produce:

```text
+---------+----------+------------+------------+-----------------+
|category |product_id|product_name|promotion_id|promotion_name   |
+---------+----------+------------+------------+-----------------+
|apparel  |P200      |Logo T-Shirt|NULL        |NULL             |
|drinkware|P100      |Trail Mug   |D10         |Drinkware 10% Off|
|footwear |NULL      |NULL        |D20         |Footwear 20% Off |
+---------+----------+------------+------------+-----------------+
```

This result contains the shared `drinkware` row, the left-only `apparel` row, and the right-only `footwear` row.

A full join is useful when you want to find matches and also preserve what is missing from either side.

## Cross Join

A **cross join** is different from the joins we have seen so far.

It does not use a join key. Instead, it combines every row from the left side with every row from the right side. This is also called a **Cartesian product**.

Because both datasets contain a `category` column, we will rename them in the result so that their meanings stay clear.

Here is the SQL version:

```python
spark.sql("""
    SELECT
      product_id,
      product_name,
      products.category AS product_category,
      promotion_id,
      promotion_name,
      category_promotions.category AS promotion_category
    FROM products
    CROSS JOIN category_promotions
    ORDER BY product_id, promotion_id
""").show(truncate=False)
```

Here is the DataFrame version:

```python
from pyspark.sql import functions as F


(
    products.alias("product")
    .crossJoin(category_promotions.alias("promotion"))
    .select(
        "product_id",
        "product_name",
        F.col("product.category").alias("product_category"),
        "promotion_id",
        "promotion_name",
        F.col("promotion.category").alias("promotion_category"),
    )
    .orderBy("product_id", "promotion_id")
    .show(truncate=False)
)
```

Both produce:

```text
+----------+------------+----------------+------------+-----------------+------------------+
|product_id|product_name|product_category|promotion_id|promotion_name   |promotion_category|
+----------+------------+----------------+------------+-----------------+------------------+
|P100      |Trail Mug   |drinkware       |D10         |Drinkware 10% Off|drinkware         |
|P100      |Trail Mug   |drinkware       |D20         |Footwear 20% Off |footwear          |
|P200      |Logo T-Shirt|apparel         |D10         |Drinkware 10% Off|drinkware         |
|P200      |Logo T-Shirt|apparel         |D20         |Footwear 20% Off |footwear          |
+----------+------------+----------------+------------+-----------------+------------------+
```

We have two products and two promotions, so the cross join creates four rows:

```text
2 products × 2 promotions = 4 combinations
```

Cross joins can be useful when you intentionally need every possible combination. For example, we might generate every product-promotion pair and then evaluate which combinations satisfy a more complicated eligibility rule.

But cross joins can grow very quickly. If the left side has one million rows and the right side has one thousand rows, the result has one billion rows. Spark will do exactly what we asked, even if we regret asking.

## Left Semi Join

A **left semi join** keeps left-side rows that have at least one match on the right.

Unlike an inner join, it returns only columns from the left side. The right side is used only to answer a yes-or-no question: does a match exist?

Here is the Spark SQL version:

```python
spark.sql("""
    SELECT
      product_id,
      product_name,
      category
    FROM products
    LEFT SEMI JOIN category_promotions USING (category)
    ORDER BY product_id
""").show(truncate=False)
```

Here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "left_semi")
    .select(
        "product_id",
        "product_name",
        "category",
    )
    .orderBy("product_id")
    .show(truncate=False)
)
```

Both produce:

```text
+----------+------------+---------+
|product_id|product_name|category |
+----------+------------+---------+
|P100      |Trail Mug   |drinkware|
+----------+------------+---------+
```

The Trail Mug remains because its `drinkware` category has a promotion. The Logo T-Shirt is removed because no promotion has the `apparel` category.

If you have used PostgreSQL, this is similar to writing a correlated `EXISTS` condition:

```sql
SELECT
  product.product_id,
  product.product_name,
  product.category
FROM products AS product
WHERE EXISTS (
  SELECT 1
  FROM category_promotions AS promotion
  WHERE promotion.category = product.category
)
ORDER BY product.product_id
```

PostgreSQL does not use `LEFT SEMI JOIN` syntax, but `EXISTS` expresses the same question: does at least one matching promotion exist for this product?

The `1` in `SELECT 1` is conventional. The database does not need a value from the matching row. It only needs to know whether the subquery found a row at all.

## Left Anti Join

A **left anti join** keeps left-side rows that do not have a match on the right.

Like a left semi join, it returns only columns from the left side. But this time, the existence test is reversed.

Here is the Spark SQL version:

```python
spark.sql("""
    SELECT
      product_id,
      product_name,
      category
    FROM products
    LEFT ANTI JOIN category_promotions USING (category)
    ORDER BY product_id
""").show(truncate=False)
```

Here is the DataFrame version:

```python
(
    products
    .join(category_promotions, "category", "left_anti")
    .select(
        "product_id",
        "product_name",
        "category",
    )
    .orderBy("product_id")
    .show(truncate=False)
)
```

Both produce:

```text
+----------+------------+--------+
|product_id|product_name|category|
+----------+------------+--------+
|P200      |Logo T-Shirt|apparel |
+----------+------------+--------+
```

The Logo T-Shirt remains because its `apparel` category does not have a promotion.

In PostgreSQL, the equivalent pattern uses `NOT EXISTS`:

```sql
SELECT
  product.product_id,
  product.product_name,
  product.category
FROM products AS product
WHERE NOT EXISTS (
  SELECT 1
  FROM category_promotions AS promotion
  WHERE promotion.category = product.category
)
ORDER BY product.product_id
```

A simple way to remember the pair is:

- left semi keeps left rows where a match exists
- left anti keeps left rows where a match does not exist

## Comparing the Join Types

Here is the overall picture for our example:

| Join type | What it preserves         | Right-side columns in result? | Rows |
| --------- | ------------------------- | ----------------------------- | ---: |
| Inner     | Matching rows only        | Yes                           |    1 |
| Left      | All left rows             | Yes                           |    2 |
| Right     | All right rows            | Yes                           |    2 |
| Full      | All rows from both sides  | Yes                           |    3 |
| Cross     | Every possible pair       | Yes                           |    4 |
| Left semi | Left rows with a match    | No                            |    1 |
| Left anti | Left rows without a match | No                            |    1 |

There is no single best join type. The right choice depends on which rows you need to preserve.

If you need only matches, use an inner join. If one side must remain complete, use a left or right join. If both sides must remain complete, use a full join. If you intentionally need every possible combination, use a cross join. And if you only need to test whether a match exists, a semi or anti join often expresses that intention clearly.

## A Practical Note About Left and Right

Which dataset goes on each side first determines the meaning of the join. For example, the side that we need to preserve must be on the left of a left join. Correct results always come before performance.

When either orientation would express the same result, however, the relative sizes of the datasets can be a useful guide. A practical starting point is:

- for a left outer, left semi, or left anti join, put the larger dataset on the left and the smaller dataset on the right
- for a right outer join, put the smaller dataset on the left and the larger dataset on the right

This arrangement often gives Spark better options for working with the smaller side of the join. Internally, Spark may broadcast a small dataset, build a lookup structure, or sort and shuffle data. We do not need those details yet; the useful beginner rule is to keep the smaller dataset on the non-preserved side when the join semantics allow it.

This is a guideline, not a promise. Spark's optimizer can choose a different strategy based on table statistics and runtime information. For large production jobs, we should inspect the execution plan and measure the job instead of relying only on which side appears first in the code.

## Summary

In this part, we paused our larger data-processing jobs and looked more closely at joins.

Using the same product and promotion datasets, we compared inner, left, right, full, cross, left semi, and left anti joins in both Spark SQL and the DataFrame API.

The main difference between join types is which rows they preserve. Inner joins keep matches. Left, right, and full joins preserve unmatched rows from particular sides. Cross joins create every possible pair. Semi and anti joins use the right side only to test whether a match exists.

With that mental model in place, we are ready to return to our batch pipelines.

In the next part, we will look at a limitation of the `INSERT INTO` pattern we have been using and introduce Iceberg’s `MERGE INTO` statement to make those jobs safer to retry.