13.2.10.2 JOIN 子句_MySQL 8.0 参考手册

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.2 数据操作语句 / 13.2.10 SELECT 语句 / 13.2.10.2 JOIN 子句

13.2.10.2 JOIN 子句

MySQL支持part of 语句和multiple-table and 语句的JOIN语法如下： table_referencesSELECTDELETEUPDATE

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

表引用也称为连接表达式。

表引用（当它指的是分区表时）可能包含一个PARTITION子句，包括以逗号分隔的分区、子分区或两者的列表。此选项跟在表的名称之后并在任何别名声明之前。此选项的效果是仅从列出的分区或子分区中选择行。任何未在列表中命名的分区或子分区都将被忽略。有关更多信息和示例，请参阅第 24.5 节，“分区选择”。

与标准 SQL 相比，MySQL 中的语法得到了table_factor扩展。该标准只接受table_reference，而不是一对括号内的列表。

如果项目列表中的每个逗号 table_reference都被视为等同于内部联接，则这是一个保守的扩展。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

相当于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

在 MySQL 中，JOIN, CROSS JOIN, 和INNER JOIN是句法等价物（它们可以相互替换）。在标准 SQL 中，它们是不等价的。INNER JOIN与ON从句一起CROSS JOIN 使用，否则使用。

通常，在仅包含内部连接操作的连接表达式中可以忽略括号。MySQL 还支持嵌套连接。请参阅第 8.2.1.8 节，“嵌套连接优化”。

可以指定索引提示来影响 MySQL 优化器如何使用索引。有关详细信息，请参阅第 8.9.4 节，“索引提示”。优化器提示和 optimizer_switch系统变量是影响优化器使用索引的其他方式。请参阅第 8.9.3 节，“优化器提示”和第 8.9.2 节，“可切换优化”。

以下列表描述了编写连接时要考虑的一般因素：

可以使用 or 为表引用起别名： tbl_name AS alias_nametbl_name alias_name

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

A在子句table_subquery中也称为派生表或子查询。FROM请参阅第 13.2.11.8 节，“派生表”。这样的子查询必须包含一个别名来为子查询结果提供一个表名，并且可以选择在括号中包含一个表列名列表。一个简单的例子如下：
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
单个连接中可以引用的最大表数为 61。这包括通过将FROM 子句中的派生表和视图合并到外部查询块中处理的连接（请参阅第 8.2.2.4 节，“优化派生表，视图引用，以及具有合并或物化功能的公用表表达式”）。
INNER JOIN和, （逗号）在没有连接条件的情况下在语义上是等价的：两者都在指定的表之间产生笛卡尔积（也就是说，第一个表中的每一行都连接到第二个表中的每一行）。
但是，逗号运算符的优先级低于 , INNER JOIN, CROSS JOIN,LEFT JOIN等。如果在存在连接条件时将逗号连接与其他连接类型混合使用，则可能会出现格式错误。本节后面将提供有关处理此问题的信息。 Unknown column 'col_name' in 'on clause'
search_conditionused with ON是可以在WHERE子句中使用的形式的任何条件表达式。通常，该ON子句用于指定如何连接表的条件，并且该 WHERE子句限制要包含在结果集中的行。
ON如果 a或USINGa 中的部分右表没有匹配行，则将 LEFT JOIN所有列设置为的行 NULL用于右表。您可以使用此事实在一个表中查找在另一个表中没有对应项的行：
```
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;
```
此示例查找具有不存在于中 left_tbl 的值的所有行（即，在中没有对应行的所有行）。请参阅第 8.2.1.9 节，“外部连接优化”。 idright_tblleft_tblright_tbl
该子句命名必须存在于两个表中的列列表。如果表和都包含列、和，则以下连接比较两个表中的相应列： USING(join_column_list)abc1c2c3
```
a LEFT JOIN b USING (c1, c2, c3)
```
NATURAL [LEFT] JOINof two tables 被定义为在语义上等同于 anINNER JOIN或 a LEFT JOINwith a USINGclause that names all the columns exist in both tables .
RIGHT JOIN类似于 LEFT JOIN. 为了保持代码在数据库之间的可移植性，建议您使用LEFT JOIN而不是RIGHT JOIN.
连接语法描述中显示的{ OJ ... }语法仅用于与 ODBC 兼容。语法中的花括号应该按字面意思写；它们不是语法描述中其他地方使用的元语法。
```
SELECT left_tbl.*
    FROM { OJ left_tbl LEFT OUTER JOIN right_tbl
           ON left_tbl.id = right_tbl.id }
    WHERE right_tbl.id IS NULL;
```
您可以在中使用其他类型的联接{ OJ ... }，例如INNER JOIN或 RIGHT OUTER JOIN。这有助于与某些第三方应用程序兼容，但不是官方的 ODBC 语法。
STRAIGHT_JOIN与类似 JOIN，只是左表总是先于右表读取。这可用于连接优化器以次优顺序处理表的那些（少数）情况。

一些加入示例：

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

自然连接和连接 with USING，包括外部连接变体，根据 SQL:2003 标准进行处理：

连接的冗余列NATURAL不会出现。考虑这组语句：
```
CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
```
在第一SELECT 条语句中，columnj出现在两个表中，因此成为一个连接列，因此，根据标准 SQL，它应该在输出中只出现一次，而不是两次。类似地，在第二个 SELECT 语句中，列 j在子句中命名 USING并且在输出中应该只出现一次，而不是两次。
因此，这些语句会产生以下输出：
```
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
```
冗余列消除和列排序根据标准 SQL 发生，产生此显示顺序：
- 首先，合并两个连接表的公共列，按照它们在第一个表中出现的顺序
- 其次，第一个表独有的列，按照它们在该表中出现的顺序
- 第三，第二个表特有的列，按照它们在该表中出现的顺序
替换两个公共列的单个结果列是使用合并操作定义的。也就是说，对于两个 t1.a和t2.a生成的单个连接列a定义为a = COALESCE(t1.a, t2.a)，其中：
```
COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)
```
如果连接操作是任何其他连接，则连接的结果列由连接表的所有列的串联组成。
合并列定义的结果是，对于外部联接，NULL如果两列之一始终为，则合并列包含非列的值NULL。如果两个列都不是NULL，则两个公共列都具有相同的值，因此选择哪个列作为合并列的值并不重要。解释这一点的一种简单方法是考虑外部联接的合并列由 a 的内部表的公共列表示JOIN。假设表t1(a, b)和 t2(a, c)具有以下内容：
```
t1    t2
----  ----
1 x   2 z
2 y   3 w
```
然后，对于此连接，列a包含以下值t1.a：
```
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+
```
相比之下，对于此连接，columna 包含的值t2.a。
```
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+
```
将这些结果与其他等效查询进行比较JOIN ... ON：
```
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    1 | x    | NULL | NULL |
|    2 | y    |    2 | z    |
+------+------+------+------+
```
```
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+
```
USING子句可以重写为比较相应列的子句ON。然而，虽然USING和 ON相似，但并不完全相同。考虑以下两个查询：
```
a LEFT JOIN b USING (c1, c2, c3)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3
```
关于确定哪些行满足连接条件，两个连接在语义上是相同的。
关于确定要显示哪些列以进行 SELECT *扩展，这两个连接在语义上并不相同。USING连接选择相应列的合并值，而连接ON选择所有表中的所有列。对于USING联接， SELECT *选择以下值：
```
COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)
```
对于ON联接，SELECT *选择以下值：
```
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
```
对于内部联接，与 one orCOALESCE(a.c1, b.c1)相同，因为两列具有相同的值。使用外连接（例如），两列之一可以是。结果中省略了该列。 a.c1b.c1LEFT JOINNULL
ON子句只能引用其操作数。
例子：
```
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
```
该语句因Unknown column 'i3' in 'on clause'错误而失败，因为i3是中的一列t3，它不是ON子句的操作数。要使连接能够被处理，重写语句如下：
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
```
JOIN比逗号运算符 ( ) 具有更高的优先级,，因此连接表达式 t1, t2 JOIN t3被解释为 (t1, (t2 JOIN t3))，而不是((t1, t2) JOIN t3)。这会影响使用 ON子句的语句，因为该子句只能引用连接操作数中的列，并且优先级会影响对这些操作数的解释。
例子：
```
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
```
TheJOIN优先于逗号运算符，因此ON 子句的操作数是t2and t3。因为t1.i1不是任何一个操作数中的列，所以结果是Unknown column 't1.i1' in 'on clause'错误的。
要使连接能够被处理，请使用以下任一策略：
- 将前两个表明确地用括号分组，以便该ON 子句的操作数是(t1, t2)and t3：
```
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
```
- 避免使用逗号运算符， JOIN而是使用：
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
```
相同的优先级解释也适用于将逗号运算符与INNER JOIN, CROSS JOIN, LEFT JOIN, 和混合使用的语句RIGHT JOIN，所有这些都具有比逗号运算符更高的优先级。
与 SQL:2003 标准相比，MySQL 的一个扩展是 MySQL 允许您限定NATURALorUSING 连接的公共（合并）列，而标准不允许这样做。