Join types (inner/left/right/full/semi/anti/cross)

Apache Spark · Aggregations & Joins

Theory

df.join(other, on, how) combines two DataFrames on matching keys. inner keeps only rows with a match on both sides. left (left_outer) keeps every left row and fills right-side columns with NULL when there is no match; right does the opposite; full (outer) keeps all rows from both sides. left_semi keeps left rows that have at least one match but returns only left columns, like WHERE EXISTS, and left_anti keeps left rows with no match, like NOT EXISTS. crossJoin pairs every left row with every right row.

Pass on='customer_id' (or a list of names) when the key has the same name on both sides, so it appears once in the result; use an expression such as orders.customer_id == customers.customer_id for different names or extra conditions. For example, joining orders to customers: inner drops order 109 from unknown customer 7, left keeps it with name NULL, and left_anti on customers finds Vikram, who never ordered.

Why we use it

Real data is spread across tables: orders, customers, products. Joins bring them together, and choosing the wrong type silently loses rows (inner instead of left) or duplicates them (many-to-many keys).

When to use it

Use inner for matched data, left to keep everything from the main table, full for reconciliations, semi/anti for 'has / has no match' filters without adding columns, and cross only for small tables such as a date x product grid. Check key uniqueness first to avoid row explosions.

How it works

  1. Two DataFrames and a join key
  2. Need rows without a match? Yes: Choose left, right or full. No: Use inner.
  3. Only need to filter by existence? Yes: Use left_semi or left_anti. No: Use a normal join to add columns.
  4. Check that keys are unique enough to avoid row explosion
  5. Joined DataFrame

Syntax

a.join(b, "key", "inner")    # how: inner | left | right | full | left_semi | left_anti
a.join(b, ["k1", "k2"], "left")
a.join(b, a.id == b.cust_id, "left")
a.crossJoin(b)

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

customers = spark.createDataFrame([
    (1, "Asha",   "Jaipur",    "Retail"),
    (2, "Ravi",   "Pune",      "Corporate"),
    (3, "Meera",  "Delhi",     "Retail"),
    (4, "Karan",  "Jaipur",    "SMB"),
    (5, "Priya",  "Mumbai",    "Corporate"),
    (6, "Vikram", "Bengaluru", "SMB"),
], "customer_id INT, name STRING, city STRING, segment STRING")

# one extra order from customer 7, who is not in customers
orders = orders.unionByName(spark.createDataFrame(
    [(109, 7, "East", "Mouse", 1, 800.0, None)], orders.schema))

for how in ["inner", "left", "right", "full", "left_semi", "left_anti"]:
    print(f"{how:10} -> {orders.join(customers, 'customer_id', how).count()} rows")

# left join keeps order 109 with NULL customer columns
(orders.join(customers, "customer_id", "left")
       .select("order_id", "customer_id", "name", "city")
       .orderBy("order_id").show())

# customers who never ordered (anti) and who did (semi)
customers.join(orders, "customer_id", "left_anti").show()
print(sorted(r.name for r in customers.join(orders, "customer_id", "left_semi").collect()))
print("cross join rows:", customers.crossJoin(orders.select("region").distinct()).count())

# Output:
# inner      -> 8 rows
# left       -> 9 rows
# right      -> 9 rows
# full       -> 10 rows
# left_semi  -> 8 rows
# left_anti  -> 1 rows
# +--------+-----------+-----+------+
# |order_id|customer_id| name|  city|
# +--------+-----------+-----+------+
# |     101|          1| Asha|Jaipur|
# |     102|          2| Ravi|  Pune|
# |     103|          1| Asha|Jaipur|
# |     104|          3|Meera| Delhi|
# |     105|          4|Karan|Jaipur|
# |     106|          2| Ravi|  Pune|
# |     107|          5|Priya|Mumbai|
# |     108|          3|Meera| Delhi|
# |     109|          7| NULL|  NULL|
# +--------+-----------+-----+------+
#
# +-----------+------+---------+-------+
# |customer_id|  name|     city|segment|
# +-----------+------+---------+-------+
# |          6|Vikram|Bengaluru|    SMB|
# +-----------+------+---------+-------+
#
# ['Asha', 'Karan', 'Meera', 'Priya', 'Ravi']
# cross join rows: 24

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic