JOIN
說明
SQL join 用於根據 join 準則合併來自兩個關係的列。以下部分說明整體 join 語法，而子部分則涵蓋不同類型的 join 及範例。
語法
relation { [ join_type ] JOIN [ LATERAL ] relation [ join_criteria ] | NATURAL join_type JOIN [ LATERAL ] relation }
參數
-
relation
指定要加入的關係。
-
join_type
指定 join 類型。
語法
[ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTI -
join_criteria
指定如何將來自一個關係的列與另一個關係的列合併。
語法：
ON boolean_expression | USING ( column_name [ , ... ] )boolean_expression指定回傳類型為布林的表達式。
Join 類型
內部 Join
內部 Join 是 Spark SQL 中的預設 join。它會選取在兩個關係中都有相符值的列。
語法
relation [ INNER ] JOIN relation [ join_criteria ]
左外部 Join
左外部 Join 會回傳左關係中的所有值，以及右關係中的相符值，或是在沒有相符值時附加 NULL。它也稱為左外部 Join。
語法
relation LEFT [ OUTER ] JOIN relation [ join_criteria ]
右外部 Join
右外部 Join 會回傳右關係中的所有值，以及左關係中的相符值，或是在沒有相符值時附加 NULL。它也稱為右外部 Join。
語法
relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]
完整外部 Join
完整外部 Join 會回傳來自兩個關係的所有值，並在沒有相符值的一側附加 NULL 值。它也稱為完整外部 Join。
語法
relation FULL [ OUTER ] JOIN relation [ join_criteria ]
交叉 Join
交叉 Join 會回傳兩個關係的笛卡兒積。
語法
relation CROSS JOIN relation [ join_criteria ]
半外部 Join
半聯結會傳回關係左邊與右邊相符的值。它也稱為左半聯結。
語法
relation [ LEFT ] SEMI JOIN relation [ join_criteria ]
反聯結
反聯結會傳回關係左邊與右邊不相符的值。它也稱為左反聯結。
語法
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|
+---+-----+------+