JOINJOIN
根據聯接準則結合兩個關聯的資料列。Combines rows from two relations based on join criteria.
語法Syntax
relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type JOIN relation }
參數Parameters
關係relation
要聯結的關聯性。The relation to be joined.
join_typejoin_type
聯結類型。The join type.
語法：Syntax:
[ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTIjoin_criteriajoin_criteria
指定某個關聯性的資料列如何與另一個關聯的資料列結合。Specifies how the rows from one relation is combined with the rows of another relation.
語法：
ON boolean_expression | USING ( column_name [ , ... ] )Syntax:ON boolean_expression | USING ( column_name [ , ... ] )boolean_expression傳回型別為布林值的運算式。An expression with a return type of Boolean.
聯結類型Join Types
內部聯結Inner Join
傳回在這兩個關聯中具有相符值的資料列。Returns rows that have matching values in both relations. 預設聯結。The default join.
語法：Syntax:
relation [ INNER ] JOIN relation [ join_criteria ]
左方聯結Left Join
從左邊的關聯性傳回所有值，並從右邊關聯傳回相符的值，如果沒有相符的值，則 NULL 會附加。Returns all values from the left relation and the matched values from the right relation, or appends NULL if there is no match. 它也稱為左方外部聯結。It is also referred to as a left outer join.
語法：Syntax:
relation LEFT [ OUTER ] JOIN relation [ join_criteria ]
靠右聯結Right Join
傳回右邊關聯中的所有值，以及左邊關聯的相符值，如果沒有相符的值，則 NULL 會附加。Returns all values from the right relation and the matched values from the left relation, or appends NULL if there is no match. 它也稱為右方外部聯結。It is also referred to as a right outer join.
語法：Syntax:
relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]
完整聯結Full Join
傳回兩個關聯中的所有值， NULL 並在沒有相符項的側邊附加值。Returns all values from both relations, appending NULL values on the side that does not have a match. 它也稱為完整外部聯結。It is also referred to as a full outer join.
語法：Syntax:
relation FULL [ OUTER ] JOIN relation [ join_criteria ]
交叉聯結Cross Join
傳回兩個關聯的笛卡兒乘積。Returns the Cartesian product of two relations.
語法：Syntax:
relation CROSS JOIN relation [ join_criteria ]
半聯結Semi Join
從與右邊相符的關聯性左邊傳回值。Returns values from the left side of the relation that has a match with the right. 它也稱為左方半聯結。It is also referred to as a left semi join.
語法：Syntax:
relation [ LEFT ] SEMI JOIN relation [ join_criteria ]
反聯結Anti Join
從左邊的關聯傳回與右邊不相符的值。Returns values from the left relation that has no match with the right. 它也稱為左方反聯結。It is also referred to as a left anti join.
語法：Syntax:
relation [ LEFT ] ANTI JOIN relation [ join_criteria ]
範例Examples
-- 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|
+---+-----+------+