Join 在 SQL 中非常常用，但通常用到的是 inner join, left join, right join, outer join。這邊介紹什麼是 semi join 與 anti join，並且用 PySpark 做範例。
情境
你在一家電商公司工作，通常客戶資料跟交易資料不會存在同一張表上，想要把客戶姓名跟交易紀錄關聯起來就必須使用到 join。有一張 customers 表儲存了客戶姓名跟居住城市，另一張 transections 表儲存了客戶 ID、居住城市跟購買金額如下：
from pyspark.sql import SparkSession
from pyspark.sql import Row
spark = SparkSession.builder.getOrCreate()
df_customers = spark.createDataFrame([
Row(id=1, name='John', city='CityA'),
Row(id=2, name='Irene', city='CityB'),
Row(id=3, name='Allen', city='CityA'),
])
df_customers.createOrReplaceTempView("customers")
df_transections = spark.createDataFrame([
Row(id=1, transection_value=100, city='CityA'),
Row(id=3, transection_value=20, city='CityA'),
])
df_transections.createOrReplaceTempView("transections")
今天想要找出居住在有購買紀錄的城市的購買的客戶姓名，可以這樣做：
spark.sql("""
SELECT
name
FROM customers
WHERE city IN (
SELECT DISTINCT(city)
FROM transections
)
""").show()
輸出：
+-----+
| name|
+-----+
| John|
|Allen|
+-----+
相反，想要找出居住在沒有購買紀錄的城市的購買的客戶姓名，可以這樣做：
spark.sql("""
SELECT
name
FROM customers
WHERE city NOT IN (
SELECT DISTINCT(city)
FROM transections
)
""").show()
輸出：
+-----+
| name|
+-----+
|Irene|
+-----+
Semi Join
只需要表 A 的資料，但是需要表 A 跟表 B 同時有出現，這時就可以使用 semi join，符合我們只需要 customers 的資訊，但是需要 city 同時出現在 customers 跟 transections：
spark.sql("""
SELECT
c.name
FROM customers c
LEFT SEMI JOIN transections t ON c.city = t.city
""").show()
輸出：
+-----+
| name|
+-----+
| John|
|Allen|
+-----+
Anti Join
anti join 則與 semi join 相反，只需要表 A 的資料，但是需要資料不出現在表 B，想要找出居住在沒有購買紀錄的城市的購買的客戶姓名，可以這樣做：
spark.sql("""
SELECT
c.name
FROM customers c
LEFT ANTI JOIN transections t ON c.city = t.city
""").show()
輸出：
+-----+
| name|
+-----+
|Irene|
+-----+
附註：並非每種 SQL 都有 semi join 跟 anti join 語法。