JOIN (SQL Analytics)JOIN (SQL Analytics)

根据联接条件合并两个关系中的行。Combines rows from two relations based on join criteria.

语法Syntax

relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type JOIN relation }

参数Parameters

  • relationrelation

    要联接的关系。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

    返回类型为布尔类型的表达式。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

返回左侧关系中的所有值和右侧关系中的匹配值;如果没有匹配值,则追加 NULLReturns 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 JoinRight Join

返回右侧关系中的所有值和左侧关系中的匹配值;如果没有匹配值,则追加 NULLReturns 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 JoinFull 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 JoinSemi 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 JoinAnti 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|
+---+-----+------+