Published November 14, 2026 · By Henry Palma

PySpark Joins and Aggregations: Inner vs Left Joins on Databricks Free Edition

Join and aggregate data with PySpark on Databricks: check lookup keys, choose inner or left joins, see how duplicate keys inflate totals, and reconcile results.

A join can run without errors and still give you the wrong total. This post follows the video's PySpark lesson on Databricks Free Edition: check your lookup keys, choose between an inner and a left join based on the question you are answering, aggregate by region, and reconcile the numbers before you save anything.

Watch on YouTube

The notebook, source file and cheat sheet are in the lesson folder on GitHub. Use a Python notebook on Serverless compute. The example is self-contained, so you do not need tables from earlier lessons.

Know the grain of every table

The grain is what one row represents. The orders table has one row per order. The customer lookup has one row per customer. The final summary will have one row per reporting region. Keeping that clear helps you catch code that runs fine but produces wrong totals.

The sample data

There are four fictional customers and six orders:

Ana has two orders, Bo and Cy have one each, and Dee has none.
Order 105 points to customer 99, who is not in the lookup.
Order 106 has no customer key at all.

The total across all six orders is 68.00. Write that number down. You will use it to check the join and the summary.

Check the lookup keys first

A customer ID can appear many times in the orders table. It should appear only once in the customer lookup.

duplicate_customer_keys = (customers.groupBy("customer_id").count()
                           .filter(F.col("count") > 1))
assert customers.filter(F.col("customer_id").isNull()).count() == 0
assert duplicate_customer_keys.count() == 0, "Customer keys are not unique; fix lookup first"

Do not drop duplicates blindly just to make this check pass. If two customer records disagree about a region, you need a business rule for which one is right.

Inner join: matched orders only

o = orders.alias("o")
c = customers.alias("c")
inner_orders = (o.join(c, F.col("o.customer_id") == F.col("c.customer_id"), "inner")
    .select("o.order_id", F.col("o.customer_id").alias("customer_id"),
            "c.customer_name", "c.region", "o.order_total"))

Short aliases make the join condition clear, and selecting the exact output columns avoids ambiguous duplicate names.

The inner join keeps four orders and 56.00 in revenue. Order 105 (customer 99) and order 106 (null key) drop out. Ordinary equality never matches null to null. Dee does not appear, because being in the lookup does not create an order. That 56.00 is matched-order revenue, not total revenue.

Left join: keep every order

Put orders on the left and use a left join to keep every order, with customer columns filled in where a match exists:

left_orders = (o.join(c, F.col("o.customer_id") == F.col("c.customer_id"), "left")
    .select("o.order_id", F.col("o.customer_id").alias("customer_id"),
            "c.customer_name", "c.region", "o.order_total",
            F.col("c.customer_id").isNotNull().alias("customer_found")))

The customer_found flag is false for the two unmatched orders. All six orders and the full 68.00 are preserved.

Two cautions from the lesson:

Filtering on right-side columns right after a left join can throw away the unmatched rows you meant to keep.
A left join keeps each left row at least once, but duplicate lookup keys can create extra rows. The uniqueness check is what guarantees six rows here.

What a duplicate key does to your totals

The notebook builds a deliberately bad lookup by repeating Ana's record. It is a read-only demonstration and is never saved.

bad_customers = customers.unionByName(customers.filter(F.col("customer_id") == 1))
bad_join = orders.join(bad_customers, "customer_id", "left")

The bad join returns 8 rows and 93.00 in revenue instead of 6 rows and 68.00. Each of Ana's two orders matched twice, adding 25.00. Nothing failed. The numbers were simply wrong. That is why key checks and reconciliation matter more than a green check mark.

Aggregate by region

Unmatched orders get an explicit bucket called UNMATCHED so they do not disappear from the report. It is a reporting label, not a real region.

reporting_orders = left_orders.withColumn(
    "report_region", F.when(~F.col("customer_found"), F.lit("UNMATCHED"))
                      .otherwise(F.col("region"))
)
region_summary = (reporting_orders.groupBy("report_region")
    .agg(F.count("*").alias("order_count"),
         F.count("customer_name").alias("matched_order_count"),
         F.sum("order_total").cast("decimal(18,2)").alias("revenue")))

groupBy changes the grain to one row per region. count("*") counts every row. count("customer_name") skips nulls, so in this controlled example it counts matched orders. In real data, a nullable column is not a reliable order counter.

| reportregion | ordercount | matchedordercount | revenue | |---|---|---|---| | NORTH | 2 | 2 | 25.00 | | SOUTH | 1 | 1 | 22.50 | | UNMATCHED | 2 | 0 | 12.00 | | WEST | 1 | 1 | 8.50 |

The buckets add up to 6 orders, 4 matched orders and 68.00, which matches the starting total.

Save the summary and verify it

The notebook creates a new schema named btjoins plus a random suffix and writes only the safe summary as a managed Delta table:

region_summary.write.format("delta").mode("error").saveAsTable(table_fqn)

mode("error") refuses to replace an existing table. If the write already succeeded, skip it and run the verification cell. The verification reads the stored table with SQL and checks every bucket, then reconciles 4 buckets, 6 orders, 4 matched orders and 68.00 revenue.

Practice challenge

Start from the safe left join, keep only matched orders, then group by region and compute order count and revenue. Predict first: you should get three real regions and 56.00 in revenue, not 68.00. Do not write over the saved summary.

Key takeaways

Define the grain, then check that lookup keys are unique and not null.
Use an inner join for matched records only and a left join to keep every row from the left side.
Count all rows with count("*"). Counting a nullable column skips nulls.
Reconcile counts and totals before you persist, then read the saved table back and check again.