MySQL :: MySQL 9.0 参考手册 :: 15.2.13.2 JOIN 子句

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / JOIN 子句

15.2.13.2 JOIN 子句

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 节，“分区选择”。

与标准 SQL 相比，MySQL 中扩展了 table_factor 的语法。标准仅接受 table_reference，而不是在括号对内的一系列 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、LEFT JOIN 等等。如果在存在联接条件的情况下混合使用逗号联接和其他联接类型，则可能会出现 Unknown column 'col_name' in 'on clause' 形式的错误。有关处理此问题的详细信息将在本节后面给出。
与 ON 一起使用的 search_condition 是可以在 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 节，“外部联接优化”。
USING(join_column_list) 子句命名必须存在于两个表中的列列表。如果表 a 和 b 都包含列 c1、c2 和 c3，则以下联接将比较来自两个表的对应列。
```
a LEFT JOIN b USING (c1, c2, c3)
```
两个表的 NATURAL [LEFT] JOIN 定义为在语义上等效于具有 USING 子句的 INNER JOIN 或 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 标准进行处理。

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 语句中，列 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)
```
如果联接操作是任何其他联接，则联接的结果列将包含联接表中所有列的串联。
合并列定义的一个结果是，对于外部联接，如果两个列中有一个始终为 NULL，则合并列将包含非 NULL 列的值。如果两个列都为 NULL 或都不为 NULL，则两个公共列具有相同的值，因此选择哪一个作为合并列的值并不重要。解释这一点的一种简单方法是，将外部联接的合并列视为 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    |
+------+------+------+
```
相反，对于此联接，列 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 中的列，而 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 混合使用的语句，所有这些语句的优先级都高于逗号运算符。
与 SQL:2003 标准相比，MySQL 的一个扩展是，MySQL 允许您限定 NATURAL 或 USING 联接的公共（合并）列，而标准不允许这样做。