JOIN
説明
SQL の JOIN は、結合条件に基づいて 2 つのリレーションから行を結合するために使用されます。以下のセクションでは、JOIN の全体的な構文と、さまざまな種類の 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)
内部結合は、Spark SQL のデフォルトの結合です。両方のリレーションに一致する値を持つ行を選択します。
構文
relation [ INNER ] JOIN relation [ join_criteria ]
左外部結合 (Left Join)
左外部結合は、左側のリレーションのすべての値と、右側のリレーションの一致する値を返し、一致しない場合は NULL を追加します。左外部結合とも呼ばれます。
構文
relation LEFT [ OUTER ] JOIN relation [ join_criteria ]
右外部結合 (Right Join)
右外部結合は、右側のリレーションのすべての値と、左側のリレーションの一致する値を返し、一致しない場合は NULL を追加します。右外部結合とも呼ばれます。
構文
relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]
完全外部結合 (Full Join)
完全外部結合は、両方のリレーションのすべての値を返し、一致しない側には NULL 値を追加します。完全外部結合とも呼ばれます。
構文
relation FULL [ OUTER ] JOIN relation [ join_criteria ]
クロス結合 (Cross Join)
クロス結合は、2 つのリレーションのデカルト積を返します。
構文
relation CROSS JOIN relation [ join_criteria ]
セミ結合 (Semi Join)
セミ結合は、右側のリレーションと一致する左側のリレーションの値を返します。左セミ結合とも呼ばれます。
構文
relation [ LEFT ] SEMI JOIN relation [ join_criteria ]
反結合 (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|
+---+-----+------+