Spark系列（四十九）Spark sql中join连接

在spark sql中，我们还会经常遇到join的使用。例如把两个表进行合并或者筛选结果等场景。在spark中使用join和在数据库中使用join差别不大。下面先介绍下spark sql中支持哪些join。

备注：

1、左半连接和左反连接是唯一的只有值来自左表的连接类型。

2、左半连接与过滤左表中只有在右表中存在键的行相同。

3、左反连接也只返回来自左表的数据，但只返回右边表中不存在的记录。

下面介绍下如何使用join的这些连接，假设我们现在有两个数据集，一个是学生的dataframe，一个是班级的dataframe。代码示例如下：

case class StudentPoJo(name: String, classId: Int, sex: String)

case class Clazz(id:Int,name:String)

var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1,"一年级"),Clazz(2,"二年级"))

val studentDF =  students.toDF()
val clazzDF =  clazzs.toDF()

接下来我们就介绍下如何使用这些join连接。

1）内连接

这个是最常见的连接，结果集是完全匹配join的相等表达式和where条件的结果集。

使用sql的方式进行内连接

studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")

session.sql("select * from student join clazz on student.classId = clazz.id").show()

使用dataframe的方式进行内连接

studentDF.join(clazzDF,studentDF.col("classId") === clazzDF.col("id")).show()

2）左外连接

这个连接的结果集是所有左边的结果集，如果左边的数据在右边没有匹配到的话，则会返回空值，例如：

代码示例如下：

使用sql的方式进行左外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student left outer join clazz on student.classId = clazz.id").show()

使用dataset的方式进行左外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.join(clazzDF, studentDF.col("classId") === clazzDF.col("id"),"left_outer").show()

3）右外连接

这个连接的结果集是所有右边的结果集，如果右边的数据在左边没有匹配到的话，则会返回空值，例如：

代码示例如下：

使用sql的方式进行右外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student right outer join clazz on student.classId = clazz.id").show()

使用dataset的方式进行右外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.join(clazzDF, studentDF.col("classId") === clazzDF.col("id"),"right_outer").show()

4）全外连接

这个连接的结果集是把上面左外和右外连接的结果集全部合并到一起，如果没匹配到的，则会返回空值，例如：

代码示例如下：

使用sql的方式进行全外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student full outer join clazz on student.classId = clazz.id").show()

使用dataset的方式进行全外连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.join(clazzDF, studentDF.col("classId") === clazzDF.col("id"),"outer").show()

5）左反连接

这个的结果集是只返回左边表的内容，条件是这些内容在右边的表里面没有匹配到，例如：

这里代表的就是左边的学生表的班级在右边的班级表里面找不到，并且只返回左边的数据

代码示例如下：

使用sql的方式进行左反连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student  left anti join clazz on student.classId = clazz.id").show()

使用dataset的方式进行左反连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.join(clazzDF, studentDF.col("classId") === clazzDF.col("id"),"left_anti").show()

6）左半连接

这个的结果集是只返回左边表的内容，条件是这些内容在右边的表里面匹配到了，例如：

这里代表的就是坐标的学生表的班级在右边的班级表里面匹配到了，并且只返回左边的数据

代码示例如下：

使用sql的方式进行左半连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student  left semi join clazz on student.classId = clazz.id").show()

使用dataset的方式进行左半连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.join(clazzDF, studentDF.col("classId") === clazzDF.col("id"),"left_semi").show()

7）交叉连接

也成为笛卡尔集连接，返回所有的组合结果，例如：

代码示例如下：

使用sql的方式进行交叉连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.createOrReplaceTempView("student")
clazzDF.createOrReplaceTempView("clazz")
session.sql("select * from student cross join clazz").show()

使用dataset的方式进行交叉连接

import session.implicits._
var students = Seq(StudentPoJo("张三", 1, "男"), StudentPoJo("李四", 2, "男"), StudentPoJo("王五", 2, "男"))
var clazzs = Seq(Clazz(1, "一年级"), Clazz(3, "三年级"))
val studentDF = students.toDF()
val clazzDF = clazzs.toDF()
studentDF.crossJoin(clazzDF).show()