需求
如何实现上图需求。先给出结论:join whre
join where
val df1: DataFrame = sc.makeRDD(Seq((1, "xm"), (2, "xl"), (3, "xw"))).toDF("id", "name")
df1.show()
df1.createOrReplaceTempView("t1")
val df2: DataFrame = sc.makeRDD(Seq(1,2)).toDF("id")
df2.createOrReplaceTempView("t2")
df2.show()
println("------------------------where------------------------------------")
val sql1:String =
s"""
|select t1.id,t1.name from t1
|left join t2
|on t1.id = t2.id
|where t2.id is NULL
|""".stripMargin
session.sql(sql1).show()
+---+----+
| id|name|
+---+----+
| 1| xm|
| 2| xl|
| 3| xw|
+---+----+
+---+
| id|
+---+
| 1|
| 2|
+---+
------------------------where------------------------------------
+---+----+
| id|name|
+---+----+
| 3| xw|
+---+----+
join and
println("------------------------and------------------------------------")
val sql2:String =
s"""
|select t1.id,t1.name from t1
|left join t2
|on t1.id = t2.id
|and t2.id is NULL
|""".stripMargin
session.sql(sql2).show()
------------------------and------------------------------------
+---+----+
| id|name|
+---+----+
| 1| xm|
| 2| xl|
| 3| xw|
+---+----+
原因分析
sql执行顺序:
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT ORDER BY TOP
详细解释连接参考我之前写的文章