Semi and anti joins

Check whether a match exists without adding right-side columns.

Step 1 of 6 · Learn

Keep matching customers

An inner join repeats customer 101 because it has two orders. A left_semi join returns each matching left row once regardless of the number of right matches, and returns only left columns. It does not deduplicate repeated rows already in the left table.

Lesson reference: PySpark and Scala

Keep matching customers

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 = customers.join(orders, "customer_id", "inner").select("customer_id", "customer_name").orderBy("customer_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 result = customers.join(orders, Seq("customer_id"), "inner").select("customer_id", "customer_name").orderBy("customer_id")
result.show()

Keep missing customers

left_anti is the complementary existence check: it retains left rows without any right match. The semi example keeps Ari and Bo; the anti version should keep Cam. No columns from orders appear in either result.

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 = customers.join(orders, "customer_id", "left_semi").select("customer_id", "customer_name").orderBy("customer_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 result = customers.join(orders, Seq("customer_id"), "left_semi").select("customer_id", "customer_name").orderBy("customer_id")
result.show()

Match qualifying orders

Filter the right table first to define what counts as a match. This example asks which lookup customers have an individual order worth at least 100. Ari has orders, but neither reaches 100. This is not a test of combined customer spending.

PySpark example

from pyspark.sql.functions import col, count

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

qualifying_orders = orders.filter(col("total") >= 100)
result = customers.join(qualifying_orders, "customer_id", "left_semi").select("customer_id", "customer_name").orderBy("customer_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 qualifying_orders = orders.filter(col("total") >= 100)
val result = customers.join(qualifying_orders, Seq("customer_id"), "left_semi").select("customer_id", "customer_name").orderBy("customer_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 = customers.join(orders, "customer_id", "inner").select("customer_id", "customer_name").orderBy("customer_id") result.show()

Left rowsMatch customer_idOutput rows

Source customers3 rows

customer_id	customer_name
101	Ari
102	Bo
106	Cam

Second input 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

Keep matching customers

Result3 rows

customer_id	customer_name
101	Ari
101	Ari
102	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.