13.2.9.2 JOIN 子句_MySQL 5.7 参考手册

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

13.2.9.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]
  | table_subquery [AS] alias
  | ( table_references )
}

joined_table: {
    table_reference [INNER | CROSS] JOIN table_factor [join_specification]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON search_condition
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [{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子句，包括以逗号分隔的分区、子分区或两者的列表。此选项跟在表的名称之后并在任何别名声明之前。此选项的效果是仅从列出的分区或子分区中选择行。任何未在列表中命名的分区或子分区都将被忽略。有关更多信息和示例，请参阅第 22.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.7 节，“嵌套连接优化”。

可以指定索引提示来影响 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.10.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.8 节，“外部连接优化”。 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 has higher precedence than the comma operator (,), so the join expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not as ((t1, t2) JOIN t3). This affects statements that use an ON clause because that clause can refer only to columns in the operands of the join, and the precedence affects interpretation of what those operands are.
Example:
```
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);
```
The JOIN takes precedence over the comma operator, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error.
To enable the join to be processed, use either of these strategies:
- Group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1, t2) and t3:
```
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
```
- Avoid the use of the comma operator and use JOIN instead:
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
```
The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.
与 SQL:2003 标准相比，MySQL 的一个扩展是 MySQL 允许您限定NATURALorUSING 连接的公共（合并）列，而标准不允许这样做。