Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 48 ยท DataFrames and Spark SQL

Joins

Combine Spark DataFrames with inner, outer, semi, anti and cross joins, write safe join conditions, avoid duplicate columns and handle null keys.

Real data lives in several tables: orders in one, customers in another, products in a third. A join combines two DataFrames by matching rows on a condition, usually equality of a key column. Joins are the most powerful and also the most expensive operation you will use, because matching rows that live on different machines requires moving data.

This lesson covers the join types, how to write join conditions without ending up with ambiguous duplicate columns, what happens with null keys, and the two strategies Spark uses to execute a join. It builds on Grouping and Aggregations and the SQL ideas from Spark SQL and Temp Views.

The real join runs on a cluster, so Spark code is read-only here. The runnable examples use pandas merge to produce the same rows, and one of them shows a place where pandas behaves differently from Spark.

The two tables

Every example uses a small customers table and an orders table. Customer 3 has no orders, and order 13 belongs to a customer who does not exist.

Python
customers = spark.createDataFrame(
    [(1, "Ann", "DE"), (2, "Bo", "FR"), (3, "Cy", "ES")],
    ["customer_id", "name", "country"])

orders = spark.createDataFrame(
    [(10, 1, 20.0), (11, 1, 35.0), (12, 2, 15.0), (13, 9, 99.0)],
    ["order_id", "customer_id", "amount"])

Join types

The general form is left.join(right, on, how). The default how is "inner".

Python
orders.join(customers, "customer_id")                 # inner
orders.join(customers, "customer_id", "left")         # left outer
orders.join(customers, "customer_id", "right")
orders.join(customers, "customer_id", "full")         # full outer
customers.join(orders, "customer_id", "left_semi")
customers.join(orders, "customer_id", "left_anti")
customers.crossJoin(orders)
how Result
inner Only rows with a match on both sides
left / left_outer All left rows; right columns are null when there is no match
right / right_outer All right rows; left columns are null when there is no match
full / outer All rows from both sides, null-filled where there is no match
left_semi Left rows that have a match; only left columns; no duplicates from multiple matches
left_anti Left rows that have no match; only left columns
cross Every left row with every right row (n times m rows)
flowchart LR
    subgraph L["Left rows"]
        A["customer 1"]
        B["customer 2"]
        C["customer 3"]
    end
    subgraph R["Right rows"]
        X["orders of 1"]
        Y["orders of 2"]
        Z["order of 9"]
    end
    A --- X
    B --- Y
    C -. "no match: left/full keep it" .-> N1["null side"]
    Z -. "no match: right/full keep it" .-> N2["null side"]

Matched pairs appear in every join; unmatched rows survive only in the outer joins, and the semi and anti joins use matching only as a test.

Semi and anti joins are filters in disguise. "Customers who have ordered" is a semi join; "customers who never ordered" is an anti join. They are often better than an inner join plus distinct, because they never multiply rows and never need the right side's columns. They also avoid the classic trap of NOT IN with nulls (see below).

A cross join has no key. It is rarely wanted, so write it explicitly with crossJoin: a join with a forgotten or always-true condition otherwise silently produces billions of rows.

Python All join types on two small tables

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Join conditions and duplicate columns

Joining on a column name (or a list of names) is the simplest form. The key column appears once in the result:

Python
orders.join(customers, "customer_id")                 # one customer_id column
orders.join(customers, ["customer_id", "country"])    # several keys

When the key names differ, or you need a condition other than equality, pass an expression. Then both key columns stay in the result:

Python
joined = orders.join(customers, orders.customer_id == customers.customer_id, "left")
joined.select("customer_id")   # AnalysisException: Reference 'customer_id' is ambiguous

Two clean ways out. Drop one of the duplicates, or rename or alias the DataFrames and refer to columns through the alias:

Python
joined = orders.join(customers, orders.customer_id == customers.customer_id, "left") \
               .drop(customers.customer_id)

o = orders.alias("o")
c = customers.alias("c")
o.join(c, F.col("o.customer_id") == F.col("c.customer_id"), "left") \
 .select("o.order_id", "c.name", "o.amount")

The second form also solves self-joins, where the same table appears twice (employees and their managers). Remember to alias both sides, otherwise Spark cannot tell the two copies apart.

Conditions can be any boolean expression, so non-equi joins are possible (o.amount > c.credit_limit, time ranges, between). These are much slower: Spark cannot use hashing and may fall back to a nested loop. Use equality conditions whenever you can, and add range conditions as extra filters on top.

Joining the same column name from both sides is also a place where columns that are not keys collide. If both DataFrames have a column named name, the result has two columns with that name, and any later reference is ambiguous. Rename before joining (withColumnRenamed) to avoid this.

Null keys

In SQL semantics, NULL = NULL is unknown, not true. So rows whose join key is null never match anything, not even another null:

  • inner join: null-key rows from either side disappear;
  • left join: null-key rows on the left are kept, with nulls on the right;
  • anti join: null-key rows on the left are kept (they never match), which is usually what you want, and the reason anti joins are safer than NOT IN (a single null in a NOT IN list makes the whole condition unknown for every row).

If you do want nulls to match, use the null-safe operator eqNullSafe in the condition (<=> in SQL), or replace nulls by a sentinel before joining. Be sure that is meaningful for your data.

Here pandas behaves differently from Spark: merge matches NaN with NaN. The stand-in shows the difference and fixes it by removing null keys before an inner join.

Python Null keys never match in Spark (but do in pandas)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

How Spark executes a join

Matching rows from two big DataFrames means getting rows with equal keys onto the same executor. Spark chooses between two main strategies.

Shuffle (sort-merge) join. Both sides are shuffled by the join key, sorted, and merged partition by partition. This is the default for two large tables and works at any size, but moves both tables across the network. A shuffle hash join is a variant that builds hash tables instead of sorting.

Broadcast hash join. If one side is small, Spark sends a full copy of it to every executor and joins locally. The big side is not shuffled at all. Spark does this automatically when it estimates a table is smaller than spark.sql.autoBroadcastJoinThreshold (10 MB by default), and you can request it:

Python
from pyspark.sql.functions import broadcast

orders.join(broadcast(customers), "customer_id")
customers.hint("broadcast")         # the same request through a hint

Broadcasting a table that is too large for executor memory causes out-of-memory errors, so use it for dimension-sized tables. Adaptive query execution can even switch to a broadcast join at run time when it sees that one side turned out small. The tuning side of this, including skewed keys, is in Performance Tuning, and the shuffle itself is explained in Shuffles and Stages.

Python Shuffle join versus broadcast join, counted

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real versus stand-in. Spark picks the strategy from statistics, shuffles over the network, spills to disk and runs the tasks in parallel. The pandas code produces the same joined rows and counts how many rows would travel, but everything happens in one process. Null-key behavior is the one place where pandas needs extra care, as shown above.

Join recipes

Real queries need more than one key and more than equality. These patterns come up all the time.

Several keys. Pass a list of names, or combine conditions with & (and parentheses around each one):

Python
orders.join(prices, ["product_id", "region"], "inner")

orders.join(prices,
            (orders.product_id == prices.pid) & (orders.region == prices.region),
            "left")

OR conditions. A match on either of two columns is allowed, but it forces Spark off the fast hash join onto a much slower nested-loop plan. If you can, run two equality joins and union the results:

Python
cond = (a.email == b.email) | (a.phone == b.phone)
matches = a.join(b, cond, "inner")                  # works, but slow on big tables

by_email = a.join(b, "email")
by_phone = a.join(b, "phone")
matches = by_email.unionByName(by_phone).distinct() # usually much faster

Duplicate column names. Joining on an expression keeps both key columns. Joining on the name keeps one. Clean up with drop(right.key), or alias both sides (a.alias("a")) and select("a.x", "b.y"). Rename colliding non-key columns before the join.

SQL expressions. The condition can be a SQL string, and the whole join can be plain SQL on temp views (see Spark SQL and Temp Views):

Python
orders.join(customers, F.expr("orders.customer_id = customers.customer_id AND amount > 10"))

spark.sql("""
    SELECT o.order_id, c.name
    FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id
""")

Semi and anti variants. All of these express "has a match" and "has no match":

Python
customers.join(orders, "customer_id", "left_semi")           # same as WHERE EXISTS
customers.join(orders, "customer_id", "left_anti")           # same as WHERE NOT EXISTS
customers.join(orders, "customer_id", "leftsemi")            # alias spellings: semi, leftsemi, anti, leftanti

Filter by a group condition (groupBy, then join). To keep only the orders of customers whose total spend exceeds 100, compute the totals in a small DataFrame and join it back, instead of a loop:

Python
big = orders.groupBy("customer_id").agg(F.sum("amount").alias("total")).filter("total > 100")
orders.join(big.select("customer_id"), "customer_id", "left_semi")   # keeps the original order columns

A window function (sum("amount").over(Window.partitionBy("customer_id"))) reaches the same result without a join; the join form is often easier to read and lets Spark broadcast the small side.

Python Multi-key joins and an OR condition by hand

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Joining on an expression and then selecting an ambiguous key column.
  • Forgetting that null keys never match, then losing rows in an inner join or expecting them to match.
  • Using an inner join when a semi join is meant, which duplicates rows with several matches.
  • Using NOT IN where an anti join is needed and getting an empty result because of a null.
  • Creating an accidental cross join (a condition that is always true) and exploding the row count.
  • Joining two huge tables without checking key skew; one very common key makes one task do most of the work.
  • Broadcasting a table that does not fit in memory.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which join returns only customers that have at least one order, with only customer columns and no duplicates?

    A left semi join keeps left rows with a match and returns only the left columns, once per row even if there are several matches.
  2. 2. Two DataFrames both have a null in their join key column. What does an inner join on that key do with those rows?

    Null comparisons are unknown, so null-key rows do not match. Use eqNullSafe if you want them to match.
  3. 3. You join orders.customer_id == customers.customer_id and then select('customer_id'). What happens?

    Joining on an expression keeps both columns. Drop one of them or select through aliases. Joining on the column name keeps a single key column.
  4. 4. When does a broadcast hash join make sense?

    Broadcasting the small side avoids shuffling the big side. A table too large for executor memory would cause out-of-memory errors.
  5. 5. Which join type produces n times m rows for tables of n and m rows?

    A cross join pairs every left row with every right row, so use it deliberately.

Summary

  • join(other, on, how) supports inner, left, right, full, left_semi, left_anti and cross joins; the default is inner.
  • Semi and anti joins act as filters: "has a match" and "has no match".
  • Joining on column names keeps one key column; joining on an expression keeps both, so drop one or use aliases (also required for self-joins).
  • Null keys never match in Spark; use eqNullSafe when they should.
  • Large joins shuffle both sides (sort-merge); small tables can be broadcast so the big side stays put.
  • Equality conditions allow fast hash-based strategies; non-equi joins are much slower.

Next: compute over neighboring rows in Window Functions.