JOIN 子句 - 亚马逊 OpenSearch 服务

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

JOIN 子句

注意

要查看哪些 AWS 数据源集成支持此SQL命令，请参阅支持的 OpenSearch SQL命令和函数。

SQL联接用于根据联接标准合并两个关系中的行。以下部分介绍整体联接语法和不同类型的联接以及示例。

语法 

relation INNER JOIN relation [ join_criteria ]

参数

  • 关系

    指定要连接的关系。

  • 加入类型

    指定联接类型。

    语法：INNER | CROSS | LEFT OUTER

  • 加入标准

    指定如何将一个关系中的行与另一个关系的行合并。

    语法：ON boolean_expression | USING ( column_name [ , ... ] )

  • 布尔表达式

    指定返回类型为布尔值的表达式。

联接类型

  • 内部联接

    需要明确指定内部联接。它选择在两个关系中具有匹配值的行。

    语法：relation INNER JOIN relation [ join_criteria ]

  • 左键加入

    左联接返回左边关系中的所有值和右边关系中的匹配值，NULL如果没有匹配项，则追加。它也被称为左外连接。

    语法：relation LEFT OUTER JOIN relation [ join_criteria ]

  • 交叉连接

    交叉连接返回两个关系的笛卡尔乘积。

    语法：relation CROSS 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 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|
+---+-----+------+-----------|