MySQL :: MySQL 8.3 Reference Manual :: 15.2.13.2 JOIN Clause

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / JOIN Clause

15.2.13.2 连接子句

MySQL支持以下 JOIN 语法用于 table_references 部分的 SELECT 语句和多表 DELETE 和 UPDATE 语句：

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 子句，包括一个逗号分隔的分区、子分区或两者的列表。该选项在表名后面，别名声明之前。该选项的效果是仅从列出的分区或子分区中选择行。未在列表中的任何分区或子分区都将被忽略。有关更多信息和示例，请参阅第 26.5 节，“分区选择”。

MySQL 中的 table_factor 语法扩展了标准 SQL。标准 SQL 只接受 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 还支持嵌套连接。请参阅第 10.2.1.8 节，“嵌套连接优化”。

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

以下列表描述了编写连接时需要考虑的通用因素：

可以使用 tbl_name AS alias_name 或 tbl_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;

一个 table_subquery 也称为派生表或子查询在 FROM 子句中。请参阅第 15.2.15.8 节，“派生表”。这些子查询必须包括一个别名，以便将子查询结果命名为表名，并且可以选择性地包括括号中的表列名列表。一个简单的示例如下：
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
单个连接中可以引用的最大表数为 61。这包括在 FROM 子句中将派生表和视图合并到外部查询块中的连接（请参阅第 10.2.2.4 节，“使用合并或物化优化派生表、视图引用和公共表表达式”）。
INNER JOIN 和 ,（逗号）在没有连接条件的情况下是语义等效的：两者都将生成两个表之间的笛卡尔积（即每个表的每一行都与另一个表的每一行连接）。

然而，逗号运算符的优先级低于 INNER JOIN、CROSS JOIN 等。如果您在连接条件存在时混合使用逗号连接和其他连接类型，可能会出现错误，例如 Unknown column 'col_name' in 'on clause'。本节后面将提供有关解决此问题的信息。
使用 搜索条件 与 ON 的条件表达式形式，可以在 WHERE 子句中使用。通常，ON 子句用于指定如何连接表，而 WHERE 子句限制结果集中的行。
如果在 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 中具有不在 right_tbl 中的 id 值的行（即所有在 left_tbl 中没有对应行的行）。见第 10.2.1.9 节，“Outer Join 优化”。
使用 USING(join_column_list) 子句指定了必须在两个表中存在的列列表。如果表 a 和 b 都包含列 c1、c2 和 c3，那么以下连接比较两个表中的对应列：
```
a LEFT JOIN b USING (c1, c2, c3)
```
两个表的 NATURAL [LEFT] JOIN 被定义为与 INNER JOIN 或带有 USING 子句的 LEFT JOIN 语义等效。
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;

自然连接和使用 USING 的连接，包括外连接变体，都是根据 SQL:2003 标准处理的：

自然连接的冗余列不出现。考虑以下语句集：
```
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 语句中，列 j 在两个表中都存在，因此成为连接列，因此根据标准 SQL，应该只在输出中出现一次，而不是两次。类似地，在第二个 SELECT 语句中，列 j 在 USING 子句中被命名，因此应该只在输出中出现一次，而不是两次。

因此，语句产生以下输出：
```
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
```
冗余列消除和列排序根据标准 SQL 进行，产生以下显示顺序：
- 首先，两个连接表的共同列，以第一个表中的顺序出现
- 其次，第一个表中的唯一列，以该表中的顺序出现
- 最后，第二个表中的唯一列，以该表中的顺序出现
单个结果列是使用 coalesce 操作定义的，即对于两个 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)
```
如果连接操作是其他连接，则连接的结果列是连接表的所有列的串联。

外连接的 coalesced 列的定义的一个结果是，如果一个列总是 NULL，那么 coalesced 列将包含非 NULL 列的值。如果两个列都是 NULL 或都不是 NULL，那么它不关心哪个列被选择为 coalesced 列的值。一个简单的解释是，考虑外连接的 coalesced 列是 inner 表的公共列。假设表 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    |
+------+------+------+
```
相比之下，对于这个连接，列 a 包含 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
```
在内连接中，COALESCE(a.c1, b.c1) 等同于 a.c1 或 b.c1，因为这两列具有相同的值。对于外连接（例如 LEFT JOIN），其中一列可以是 NULL。该列将被从结果中省略。
一个 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);
```
JOIN 的优先级高于逗号运算符，因此 ON 子句的操作数是 t2 和 t3。因为 t1.i1 不是这两个操作数中的任何一个列，因此结果是一个 Unknown column 't1.i1' in 'on clause' 错误。

要使连接能够被处理，可以使用以下两种策略：
- 使用括号明确地分组前两个表，以便 ON 子句的操作数是 (t1, t2) 和 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 的语句，这些都具有高于逗号运算符的优先级。
MySQL 相比 SQL:2003 标准的一个扩展是，MySQL 允许您限定 NATURAL 或 USING 连接的公共（合并）列，而标准禁止这样做。

PREV HOME UP NEXT