Spark Sql之join on and和where

需求

如何实现上图需求。先给出结论：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

详细解释连接参考我之前写的文章

链接在此