Left and full joins

Preserve unmatched rows and recognize nulls introduced by a join.

Step 1 of 6 · Learn

Keep the left side

The customer lookup contains 101, 102, and 106. The inner example discards orders without a matching customer. A left join preserves every order and places null in customer_name when the lookup has no match. It does not add customer 106, which has no order.

Lesson reference: PySpark and Scala

Keep the left side

PySpark example

from pyspark.sql.functions import col, count

customers = spark.createDataFrame(
    [(101,"Ari"),(102,"Bo"),(106,"Cam")],
    "customer_id long, customer_name string"
)

result = orders.join(customers, "customer_id", "inner")
result = result.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

Scala example

import org.apache.spark.sql.functions._
import spark.implicits._

val customers = Seq(
  (101L, "Ari"),
  (102L, "Bo"),
  (106L, "Cam")
).toDF("customer_id", "customer_name")

val joined = orders.join(customers, Seq("customer_id"), "inner")
val result = joined.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

Keep both sides

A full join preserves unmatched rows from either input. Unlike the left example, it also includes customer 106 with null order_id. Joining on the named key returns a single customer_id column containing the available key from either side.

PySpark example

from pyspark.sql.functions import col, count

customers = spark.createDataFrame(
    [(101,"Ari"),(102,"Bo"),(106,"Cam")],
    "customer_id long, customer_name string"
)

result = orders.join(customers, "customer_id", "left")
result = result.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

Scala example

import org.apache.spark.sql.functions._
import spark.implicits._

val customers = Seq(
  (101L, "Ari"),
  (102L, "Bo"),
  (106L, "Cam")
).toDF("customer_id", "customer_name")

val joined = orders.join(customers, Seq("customer_id"), "left")
val result = joined.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

Find unmatched rows

After a full join, nulls identify which side was absent when the tested source field is non-null. Here order_id is always present in original orders and customer_name is always present in the lookup. The example finds orders missing customer details.

PySpark example

from pyspark.sql.functions import col, count

customers = spark.createDataFrame(
    [(101,"Ari"),(102,"Bo"),(106,"Cam")],
    "customer_id long, customer_name string"
)

result = orders.join(customers, "customer_id", "full").filter(col("customer_name").isNull())
result = result.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

Scala example

import org.apache.spark.sql.functions._
import spark.implicits._

val customers = Seq(
  (101L, "Ari"),
  (102L, "Bo"),
  (106L, "Cam")
).toDF("customer_id", "customer_name")

val joined = orders.join(customers, Seq("customer_id"), "full").filter(col("customer_name").isNull)
val result = joined.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id")
result.show()

example.pyPySpark

from pyspark.sql.functions import col, count customers = spark.createDataFrame( [(101,"Ari"),(102,"Bo"),(106,"Cam")], "customer_id long, customer_name string" ) result = orders.join(customers, "customer_id", "inner") result = result.select("customer_id", "order_id", "customer_name").orderBy("customer_id", "order_id") result.show()

Left rowsMatch customer_idOutput rows

Source orders6 rows

order_id	customer_id	status	total	item_count
1001	101	Delivered	89.5	2
1002	102	Shipped	149	3
1003	101	Cancelled	35	1
1004	103	Delivered	219.99	4
1005	104	Delivered	49.99	1
1006	105	Shipped	120	2

Second input customers3 rows

customer_id	customer_name
101	Ari
102	Bo
106	Cam

Keep the left side

Result3 rows

customer_id	order_id	customer_name
101	1001	Ari
101	1003	Ari
102	1002	Bo

Compare the source columns with the transformed result.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.