13.2.9.2 JOIN 子句

MySQL 对SELECT语句的* table_references *部分以及多 tableDELETE和UPDATE语句支持以下JOIN语法：

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] ...

table 引用也称为联接 table 达式。

table 引用(当它引用分区 table 时)可能包含PARTITION选项，包括以逗号分隔的分区，子分区或两者的列 table。该选项在 table 名之后并在任何别名声明之前。此选项的作用是仅从列出的分区或子分区中选择行。列 table 中未命名的所有分区或子分区都将被忽略。有关更多信息和示例，请参见第 22.5 节“分区选择”。

与标准 SQL 相比，MySQL 扩展了* table_factor 的语法。该标准仅接受 table_reference *，而不接受一对括号内的列 table。

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

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。

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

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

下 table 描述了编写联接时要考虑的一般因素：

table 引用可以使用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子句中也称为派生 table 或子查询。参见第 13.2.10.8 节“派生 table”。此类子查询必须(必须)包含别名，以便为子查询结果提供 table 名。一个简单的示例如下：

SELECT * FROM (SELECT 1, 2, 3) AS t1;

单个联接中最多可以引用 61 个 table。这包括通过将FROM子句中的派生 table 和视图合并到外部查询块中来处理的联接(请参见第 8.2.2.4 节“通过合并或实现来优化派生 table 和视图引用”)。

在没有连接条件的情况下，INNER JOIN和,(逗号)在语义上等效：都在指定的 table 之间产生笛卡尔积(即，第一个 table 中的每一行都连接到第二个 table 中的每一行) 。

但是，逗号运算符的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等。如果在存在联接条件时将逗号联接与其他联接类型混合使用，则可能会出现Unknown column 'col_name' in 'on clause'形式的错误。本节稍后将提供有关解决此问题的信息。

与ON一起使用的* search_condition *是可以在WHERE子句中使用的任何形式的条件 table 达式。通常，ON子句用于指定如何联接 table 的条件，而WHERE子句限制要在结果集中包括哪些行。
如果LEFT JOIN的ON或USING部分中没有右 table 的匹配行，则将所有列都设置为NULL的行用于右 table。您可以使用此事实在一个 table 中查找在另一个 table 中没有对应项的行：

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中的所有行而right_tbl中没有对应的行)。参见第 8.2.1.8 节“外部联接优化”。

USING(join_column_list)子句命名两个 table 中必须存在的列的列 table。如果 tablea和b都包含列c1，c2和c3，则以下联接比较两个 table 中的对应列：

a LEFT JOIN b USING (c1, c2, c3)

两个 table 的NATURAL [LEFT] JOIN被定义为在语义上等效于INNER JOIN或LEFT JOIN，并且带有USING子句，该子句命名两个 table 中都存在的所有列。
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类似，不同之处在于总是先读取左 table，然后再读取右 table。这可以用于联接优化器以次优 Sequences 处理 table 的那些情况(很少)。

一些连接示例：

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出现在两个 table 中，因此成为联接列，因此，根据标准 SQL，它在输出中应该只出现一次，而不是两次。同样，在第二条 SELECT 语句中，列j在USING子句中命名，并且应仅在输出中出现一次，而不是出现两次。

因此，这些语句产生以下输出：

+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+

根据标准 SQL 进行冗余列消除和列排序，从而产生以下显示 Sequences：

首先，按两个连接 table 在第一个 table 中出现的 Sequences 合并公共列
- 第二，第一个 table 的唯一列，以它们在该 table 中出现的 Sequences
- 第三，第二个 table 的唯一列，以它们在该 table 中出现的 Sequences

使用合并操作定义替换两个公共列的单个结果列。也就是说，对于两个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)

如果联接操作是任何其他联接，则联接的结果列由联接 table 的所有列的串联组成。

定义合并列的结果是，对于外部联接，如果两列之一始终为NULL，则合并列包含非NULL列的值。如果两个列都不都是NULL，则两个公共列都具有相同的值，因此选择哪个作为合并列的值都没有关系。一种简单的解释方法是考虑外部联接的合并列由JOIN的内部 table 的公共列 table 示。假设 tablet1(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联接选择所有 table 中的所有列。对于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子句只能引用其操作数。

Example:

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的优先级比逗号运算符(,)高，因此联接 table 达式t1, t2 JOIN t3被解释为(t1, (t2 JOIN t3))，而不是((t1, t2) JOIN t3)。这会影响使用ON子句的语句，因为该子句只能引用联接操作数中的列，并且优先级会影响对这些操作数的解释。

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);

JOIN优先于逗号运算符，因此ON子句的操作数为t2和t3。因为t1.i1不是两个操作数中的一列，所以结果是Unknown column 't1.i1' in 'on clause'错误。

要使连接能够被处理，请使用以下两种策略之一：

将前两个 table 明确地用括号分组，以使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连接的公共(成对)列，而标准不允许这样做。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35