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 ] ANTI

  • join_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

    返回类型为 Boolean 的表达式。An expression with a return type of Boolean.

联接类型Join Types

Inner JoinInner Join

返回在两个关系中具有匹配值的行。Returns rows that have matching values in both relations. 默认联接。The default join.

语法：Syntax:

relation [ INNER ] JOIN relation [ join_criteria ]

Left JoinLeft 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 JoinCross 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|
+---+-----+------+