MySQL :: MySQL 8.4 Reference Manual :: 15.2.13.2 JOIN Clause

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / JOIN Clause

15.2.13.2 连接子句

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

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 在与标准 SQL 相比，扩展了table_factor语法。标准只接受一个table_reference，而不是在括号内的列表。

这是一种保守的扩展，如果每个逗号在table_reference项列表中被认为是等同于 inner join。例如：

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 中，它们不是等价的。使用了 ON 子句的是 INNER JOIN，否则是 CROSS JOIN。

一般来说，在只包含内连接操作的连接表达式中，可以忽略圆括号。MySQL 还支持嵌套连接。请参阅第 10.2.1.8 节，“ Nested Join Optimization”。

索引提示可以指定，以影响 MySQL 优化器对索引的使用。更多信息，请参阅第 10.9.4 节，“ Index Hints”。优化器提示和 optimizer_switch 系统变量是影响优化器对索引使用的其他方法。请参阅第 10.9.3 节，“ Optimizer Hints” 和第 10.9.2 节，“ Switchable Optimizations”。

以下是编写连接时需要考虑的一般因素：

可以使用 tbl_名 AS alias_名 或 tbl_名 alias_名 对表引用进行别名：

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。这包括由derived tables和views在FROM子句中合并到外部查询块中的join（请参阅第10.2.2.4节，“优化派生表、视图引用和公共表达式的合并或物化”）。
INNER JOIN和,(逗号)在缺少连接条件时是语义等价的：两者都生产指定表之间的笛卡尔积（即每个行在第一个表中与每个行在第二个表中相连）。

然而，逗号操作符的优先级低于INNER JOIN、CROSS JOIN、LEFT JOIN等。如果您在存在连接条件的情况下混合使用逗号连接和其他连接类型，可能会出现以下错误：Unknown column 'col_名' in 'on clause'。关于解决这个问题的信息将在本节后面提供。
使用与ON相关的search_condition可以是任何可以在WHERE子句中使用的条件表达式。通常，ON子句用于指定如何连接表，而WHERE子句用于限制结果集中的行。
如果在LEFT JOIN中没有找到右表的匹配行，在ON或USING部分，一个所有列都设置为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中的所有行，其中id值不在right_tbl中（即left_tbl中的所有行没有对应的行在right_tbl中）。请参阅第10.2.1.9节，“Outer Join Optimization”。
USING(join_column_list)子句命名了一组必须在两个表中存在的列。如果表a和表b都包含列c1、c2和c3，以下连接比较来自两个表的相应列：
```
a LEFT JOIN b USING (c1, c2, c3)
```
两个表的NATURAL [LEFT] JOIN定义为与一个INNER JOIN或LEFT JOIN语句具有相同语义的，使用USING子句命名了两个表中的所有共享列。
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 类似，唯一的区别是左表总是先被读取，然后再读取右表。这可以用于那些 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 列的定义可见，对于外部连接，coalesced 列包含非NULL列的值，如果其中一列总是NULL。如果两列都不是NULL或都是NULL，那么这两个公共列具有相同的值，因此不管选择哪一个作为 coalesced 列的值都无所谓。可以将这个问题解释为考虑外部连接的 coalesced 列是由内表中的公共列表示的。假设表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) 等同于其中一个或两个列，因为这两个列具有相同的值。在外连接（例如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语句中，JOIN操作符优先于逗号操作符，因此ON子句的操作数是t2和t3。由于t1. i1不是这两个操作数中的列，因此结果是一个Unknown column 't1.i1' in 'on clause'错误。

要使join语句被处理，可以使用以下策略之一：
- 明确地将前两个表组合在一起，以便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 join中 qualify公共（coalesced）列，而标准禁止这样做。

PREV HOME UP NEXT