JOIN
描述
SQL join 用于基于连接条件合并两个关系的行。以下部分描述了总体连接语法，子部分涵盖了不同类型的连接以及示例。
语法
relation { [ join_type ] JOIN [ LATERAL ] relation [ join_criteria ] | NATURAL join_type JOIN [ LATERAL ] relation }
参数
-
relation
指定要连接的关系。
-
join_type
指定连接类型。
语法
[ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTI -
join_criteria
指定如何将一个关系的行与另一个关系的行合并。
语法:
ON boolean_expression | USING ( column_name [ , ... ] )boolean_expression指定返回类型为布尔值的表达式。
连接类型
Inner Join
Inner join 是 Spark SQL 中的默认连接。 它选择两个关系中都具有匹配值的行。
语法
relation [ INNER ] JOIN relation [ join_criteria ]
Left Join
Left join 返回左侧关系的所有值和右侧关系中匹配的值，如果没有匹配，则附加 NULL。 它也称为 left outer join。
语法
relation LEFT [ OUTER ] JOIN relation [ join_criteria ]
Right Join
Right join 返回右侧关系的所有值和左侧关系中匹配的值，如果没有匹配，则附加 NULL。 它也称为 right outer join。
语法
relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]
Full Join
Full join 返回两个关系的所有值，并在没有匹配的一侧附加 NULL 值。 它也称为 full outer join。
语法
relation FULL [ OUTER ] JOIN relation [ join_criteria ]
Cross Join
Cross join 返回两个关系的笛卡尔积。
语法
relation CROSS JOIN relation [ join_criteria ]
Semi Join
Semi join 返回与右侧关系匹配的左侧关系的值。 它也称为 left semi join。
语法
relation [ LEFT ] SEMI JOIN relation [ join_criteria ]
Anti Join
Anti join 返回与右侧关系不匹配的左侧关系的值。 它也称为 left anti join。
语法
relation [ LEFT ] ANTI JOIN relation [ join_criteria ]
示例
-- Use employee and department tables to demonstrate different type of joins.
SELECT * FROM employee;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|105|Chloe| 5|
|103| Paul| 3|
|101| John| 1|
|102| Lisa| 2|
|104| Evan| 4|
|106| Amy| 6|
+---+-----+------+
SELECT * FROM department;
+------+-----------+
|deptno| deptname|
+------+-----------+
| 3|Engineering|
| 2| Sales|
| 1| Marketing|
+------+-----------+
-- Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
FROM employee INNER JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno| deptname|
+---+-----+------+-----------|
|103| Paul| 3|Engineering|
|101| John| 1| Marketing|
|102| Lisa| 2| Sales|
+---+-----+------+-----------|
-- Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
FROM employee LEFT JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno| deptname|
+---+-----+------+-----------|
|105|Chloe| 5| NULL|
|103| Paul| 3|Engineering|
|101| John| 1| Marketing|
|102| Lisa| 2| Sales|
|104| Evan| 4| NULL|
|106| Amy| 6| NULL|
+---+-----+------+-----------|
-- Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
FROM employee RIGHT JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno| deptname|
+---+-----+------+-----------|
|103| Paul| 3|Engineering|
|101| John| 1| Marketing|
|102| Lisa| 2| Sales|
+---+-----+------+-----------|
-- Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
FROM employee FULL JOIN department ON employee.deptno = department.deptno;
+---+-----+------+-----------|
| id| name|deptno| deptname|
+---+-----+------+-----------|
|101| John| 1| Marketing|
|106| Amy| 6| NULL|
|103| Paul| 3|Engineering|
|105|Chloe| 5| NULL|
|104| Evan| 4| NULL|
|102| Lisa| 2| Sales|
+---+-----+------+-----------|
-- Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department;
+---+-----+------+-----------|
| id| name|deptno| deptname|
+---+-----+------+-----------|
|105|Chloe| 5|Engineering|
|105|Chloe| 5| Marketing|
|105|Chloe| 5| Sales|
|103| Paul| 3|Engineering|
|103| Paul| 3| Marketing|
|103| Paul| 3| Sales|
|101| John| 1|Engineering|
|101| John| 1| Marketing|
|101| John| 1| Sales|
|102| Lisa| 2|Engineering|
|102| Lisa| 2| Marketing|
|102| Lisa| 2| Sales|
|104| Evan| 4|Engineering|
|104| Evan| 4| Marketing|
|104| Evan| 4| Sales|
|106| Amy| 4|Engineering|
|106| Amy| 4| Marketing|
|106| Amy| 4| Sales|
+---+-----+------+-----------|
-- Use employee and department tables to demonstrate semi join.
SELECT * FROM employee SEMI JOIN department ON employee.deptno = department.deptno;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|103| Paul| 3|
|101| John| 1|
|102| Lisa| 2|
+---+-----+------+
-- Use employee and department tables to demonstrate anti join.
SELECT * FROM employee ANTI JOIN department ON employee.deptno = department.deptno;
+---+-----+------+
| id| name|deptno|
+---+-----+------+
|105|Chloe| 5|
|104| Evan| 4|
|106| Amy| 6|
+---+-----+------+