Join通过使用一个或多个表的公共值合并来自一个或多个表的列来生成新表。 它是支持SQL的数据库中的常见操作，它对应于 关系代数 加入。 一个表连接的特殊情况通常被称为 “self-join”.
语法:
SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...从表达式 ON 从子句和列 USING 子句被称为 “join keys”.
支持的联接类型
所有标准 SQL JOIN 支持类型:
INNER JOIN，只返回匹配的行。LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。
JOIN 没有指定类型暗指 INNER. 关键字 OUTER 可以省略。
ClickHouse中提供的其他联接类型:
LEFT SEMI JOIN和RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。LEFT ANTI JOIN和RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
SQL中所有关于join的用法：
- left/right outer join
- inner join
- full outer join
- left/right anti join
- left/right semi join
- cross join
本文将给出具体的数据，通过此方式说明以上join的用法；在文章开始前，首先说明一下运行环境：
语言：Spark SQL运行环境：命令行
一、准备数据
1、准备表person，并加载数据
- 创建person.txt文件
1001 张三 1002 李四 1003 王二麻子 1004 刘五 1005 毛八 1006 孙西瓜 1007 陈小坏 1009 刘妞妞
- 在数据库中创建表person
create table person ( uid int, name string ) row format delimited fields terminated by '\t' ;
- 加载数据
load data local inpath '/opt/data/person.txt' into table person ;
注：未将数据上传至hdfs，我是从本地加载
- 查看person表数据
2、准备表scorep
- 创建scorep.txt
1001 1001002 98 1004 99 1005 89 1007 88 1008 92
- 在数据库中创建表scorep.txt
create table scorep( uid int, score int ) row format delimited fields terminated by '\t' ;
- 加载数据
load data local inpath '/opt/data/scorep.txt' into table scorep ;
注：未将数据上传至hdfs，我是从本地加载
- 查看scorep表数据
二、不同JOIN用法
1、Left / Right Join
left join：以左表为主表，返回所有左表的数据；left outer join = left joinright join：以右表为主表，返回所有右表的数据；right outer join = right join
图示：
以left join 为例，SQL如下：
select * from person t1left join scorep t2on t1.uid = t2.uid
结果如下：
2、Inner Join
inner join：返回两张表的交集部分；inner join = join
SQL如下：
select * from person t1 join scorep t2 on t1.uid = t2.uid
结果如下：
3、Full Outer Join
full outer join：全外连接，返回两张表的并集；full outer join = full join
图示：
SQL如下：
select *from person t1full join scorep t2on t1.uid = t2.uid
结果如下：
4、Left / Right Anti Join
是SQL中in/exists的一种高效实现
left anti join：剔除两张表的并集，然后返回左表的数据
right anti join：剔除两张表的并集，然后返回右表的数据
图示：
以left anti join举例，SQL如下：
select *from person t1left anti join scorep t2on t1.uid = t2.uid
结果如下：
以not in方式实现，SQL如下：
select *from person where uid not in (select uid from scorep)
结果如下：
以not exists方式实现，SQL如下：
select *from person t1where not exists (select * from scorep t2 where t2.uid = t1.uid)
结果如下：
5、Left / Right Semi Join
是SQL中in/exists的一种高效实现
left semi join：左半连接
right semi join：右半连接
以left semi join为例，SQL如下：
select *from person t1left semi join scorep t2on t1.uid = t2.uid
结果如下：
以in写法实现，SQL如下：
select *from person where uid in (select uid from scorep)
结果如下：
以exists方式实现，SQL如下：
select *from person t1where exists (select * from scorep t2 where t2.uid = t1.uid)
结果如下：
6、Cross Join
cross join：返回的是两张表的笛卡尔积
SQL如下：
select * from person cross join scorep