MySQL 8.0-13.2.10.2 JOIN Clause

MySQL supports the following JOIN syntax for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:

对于SELECT语句和多表DELETE和UPDATE语句的table_references部分，MySQL支持以下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]
  | [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] ...

A table reference is also known as a join expression.

表引用也称为连接表达式。

A table reference (when it refers to a partitioned table) may contain a PARTITION clause, including a list of comma-separated partitions, subpartitions, or both. This option follows the name of the table and precedes any alias declaration. The effect of this option is that rows are selected only from the listed partitions or subpartitions. Any partitions or subpartitions not named in the list are ignored. For more information and examples, see Section 24.5, “Partition Selection”.

一个表引用(当它引用一个分区表时)可能包含一个PARTITION子句，包括逗号分隔的分区、子分区或两者的列表。这个选项在表名之后，在任何别名声明之前。此选项的效果是仅从列出的分区或子分区中选择行。列表中未命名的任何分区或子分区将被忽略。

The syntax of table_factor is extended in MySQL in comparison with standard SQL. The standard accepts only table_reference, not a list of them inside a pair of parentheses.

与标准SQL相比，table_factor的语法在MySQL中得到了扩展。该标准只接受table_reference，而不接受圆括号内的列表。

This is a conservative extension if each comma in a list of table_reference items is considered as equivalent to an inner join. For example:

如果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)

is equivalent to:

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)

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

在MySQL中，JOIN、CROSS JOIN和INNER JOIN在语法上是等价的(它们可以互相替换)。在标准SQL中，它们是不相等的。INNER JOIN与ON子句一起使用，而CROSS JOIN则使用其他方式。

In general, parentheses can be ignored in join expressions containing only inner join operations. MySQL also supports nested joins. See Section 8.2.1.8, “Nested Join Optimization”.

通常，在只包含内部连接操作的连接表达式中，括号可以被忽略。MySQL还支持嵌套连接。

Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see Section 8.9.4, “Index Hints”. Optimizer hints and the optimizer_switch system variable are other ways to influence optimizer use of indexes. See Section 8.9.3, “Optimizer Hints”, and Section 8.9.2, “Switchable Optimizations”.

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

The following list describes general factors to take into account when writing joins:

下面的列表描述了在编写连接时需要考虑的一般因素:

• A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:表引用可以使用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;

• A table_subquery is also known as a derived table or subquery in the FROM clause. See Section 13.2.11.8, “Derived Tables”. Such subqueries must include an alias to give the subquery result a table name, and may optionally include a list of table column names in parentheses. A trivial example follows: table_subquery也称为FROM子句中的派生表或子查询。参见13.2.11.8节“派生表”。这样的子查询必须包含一个别名，以便为子查询结果提供一个表名，还可以选择在括号中包含表列名列表。下面是一个简单的例子:

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

• The maximum number of tables that can be referenced in a single join is 61. This includes a join handled by merging derived tables and views in the FROM clause into the outer query block (see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”).

• 单个连接中可以引用的表的最大数量是61。这包括通过将FROM子句中的派生表和视图合并到外部查询块中来处理的连接(参见8.2.2.4节，“使用合并或物化优化派生表、视图引用和公共表表达式”)。

• INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).INNER JOIN和(逗号)在没有连接条件的情况下在语义上是等价的:它们都在指定的表之间产生一个笛卡尔乘积(即第一个表中的每一行都连接到第二个表中的每一行)。

  However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section. 但是，逗号操作符的优先级小于INNER JOIN、CROSS JOIN、LEFT JOIN等。如果在有连接条件的情况下，将逗号连接与其他连接类型混合使用，则可能出现'on子句'中的Unknown列'col_name'形式的错误。关于处理这个问题的信息将在本节的后面给出。

• The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.

• 与ON一起使用的search_condition是可以在WHERE子句中使用的任何形式的条件表达式。通常，ON子句用于指定如何连接表的条件，WHERE子句限制在结果集中包含哪些行。

• If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table: 如果在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;

  This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). See Section 8.2.1.9, “Outer Join Optimization”.这个示例查找left_tbl中所有具有right_tbl中不存在的id值的行(即，left_tbl中所有没有right_tbl中相应行的行)。

• The USING(join_column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables: USING(join_column_list)子句指定两个表中都必须存在的列的列表。如果表a和表b都包含列c1, c2和c3，下面的连接比较这两个表的相应列:

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

• The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

• 两个表的NATURAL [LEFT] JOIN在语义上等同于INNER JOIN或带有USING子句的LEFT JOIN，该子句用于命名两个表中存在的所有列。

• RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

• 右JOIN的工作方式类似于左JOIN。为了保持代码在数据库之间的可移植性，建议使用LEFT JOIN而不是RIGHT JOIN。

• The { OJ ... } syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions. {OJ…}语法的存在只是为了与ODBC兼容。语法中的花括号应该按字面意思写;它们不是语法描述中其他地方使用的元asyntax。

  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;

  You can use other types of joins within { OJ ... }, such as INNER JOIN or RIGHT OUTER JOIN. This helps with compatibility with some third-party applications, but is not official ODBC syntax. 你可以在{OJ…}，例如INNER JOIN或RIGHT OUTER JOIN。这有助于与一些第三方应用程序兼容，但不是官方的ODBC语法。

• STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.

• 与JOIN类似，只是左表总是在右表之前读取。这可以用于连接优化器以次优顺序处理表的(少数)情况。

Some join examples:

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;

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard:

自然连接和使用USING连接，包括外部连接变体，根据SQL:2003标准进行处理:

• Redundant columns of a NATURAL join do not appear. Consider this set of statements: 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);

  In the first SELECT statement, column j appears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the output, not twice. Similarly, in the second SELECT statement, column j is named in the USING clause and should appear only once in the output, not twice. 在第一个SELECT语句中，列j出现在两个表中，因此成为联接列，因此，根据标准SQL，它应该只在输出中出现一次，而不是两次。类似地，在第二个SELECT语句中，column j在USING子句中命名，并且应该只在输出中出现一次，而不是两次。

  Thus, the statements produce this output: 因此，语句产生如下输出:

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

  Redundant column elimination and column ordering occurs according to standard SQL, producing this display order:根据标准SQL进行冗余列消除和列排序，产生如下显示顺序:

  • First, coalesced common columns of the two joined tables, in the order in which they occur in the first table

  • 首先，合并两个合并表的公共列，按照它们在第一个表中出现的顺序

  • Second, columns unique to the first table, in order in which they occur in that table

  • 第二，列在第一个表中是唯一的，按它们在该表中的顺序排列

  • Third, columns unique to the second table, in order in which they occur in that table

  • 第三，第二个表中唯一的列，按它们在那个表中出现的顺序排列

  The single result column that replaces two common columns is defined using the coalesce operation. That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where: 使用合并操作定义替换两个公共列的单个结果列。也就是说，对于两个t1。和t2。结果的单个连接列定义为a = COALESCE(t1.;t2.a),地点:

  COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)

  If the join operation is any other join, the result columns of the join consist of the concatenation of all columns of the joined tables. 如果连接操作是任何其他连接，则连接的结果列由连接的表的所有列的连接组成。

  A consequence of the definition of coalesced columns is that, for outer joins, the coalesced column contains the value of the non-NULL column if one of the two columns is always NULL. If neither or both columns are NULL, both common columns have the same value, so it doesn't matter which one is chosen as the value of the coalesced column. A simple way to interpret this is to consider that a coalesced column of an outer join is represented by the common column of the inner table of a JOIN. Suppose that the tables t1(a, b) and t2(a, c) have the following contents: 合并列定义的结果是，对于外部连接，如果两个列中的一个始终为NULL，则合并列包含非NULL列的值。如果两个列都是NULL，那么两个公共列都有相同的值，因此选择哪一个作为合并列的值并不重要。一种简单的解释方法是，将外部连接的合并列表示为join的内部表的公共列。假设表t1(a, b)和t2(a, c)的内容如下:

  t1    t2
  ----  ----
  1 x   2 z
  2 y   3 w

  Then, for this join, column a contains the values of t1.a: 那么，对于这个连接，列a包含t1.a的值:

  mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
  +------+------+------+
  | a    | b    | c    |
  +------+------+------+
  |    1 | x    | NULL |
  |    2 | y    | z    |
  +------+------+------+

  By contrast, for this join, column a contains the values of t2.a. 相比之下，对于这个连接，列a包含t2.a的值。

  mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
  +------+------+------+
  | a    | c    | b    |
  +------+------+------+
  |    2 | z    | y    |
  |    3 | w    | NULL |
  +------+------+------+

  Compare those results to the otherwise equivalent queries with JOIN ... ON: 将这些结果与使用JOIN的其他等效查询进行比较…:

  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    |
  +------+------+------+------+

• A USING clause can be rewritten as an ON clause that compares corresponding columns. However, although USING and ON are similar, they are not quite the same. Consider the following two queries: 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

  With respect to determining which rows satisfy the join condition, both joins are semantically identical. 关于确定哪些行满足连接条件，两个连接在语义上是相同的。

  With respect to determining which columns to display for SELECT * expansion, the two joins are not semantically identical. The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables. For the USING join, SELECT * selects these values: 关于决定为SELECT *展开显示哪些列，这两个连接在语义上并不相同。USING连接选择相应列的合并值，而ON连接选择所有表中的所有列。对于USING连接，SELECT *选择这些值:

  COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)

  For the ON join, SELECT * selects these values: 对于ON连接，SELECT *选择以下值:

  a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

  With an inner join, COALESCE(a.c1, b.c1) is the same as either a.c1 or b.c1 because both columns have the same value. With an outer join (such as LEFT JOIN), one of the two columns can be NULL. That column is omitted from the result. 使用内连接，COALESCE(a。(c, b, c)和a, c或b，是一样的，因为这两列有相同的值。对于外部连接(例如LEFT join)，这两列中的一列可以是NULL。结果中省略了那一列。

• An ON clause can refer only to its operands. 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;

  The statement fails with an Unknown column 'i3' in 'on clause' error because i3 is a column in t3, which is not an operand of the ON clause. To enable the join to be processed, rewrite the statement as follows: 语句在'on子句'错误中出现Unknown列'i3'时失败，因为i3是t3中的列，而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. JOIN的优先级高于逗号操作符(，)，因此连接表达式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);

  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. JOIN优先于逗号操作符，因此ON子句的操作数是t2和t3。因为t1。i1在这两个操作数中都不是列，结果是Unknown列't1。I1 ' in 'on从句'错误。

  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: 将前两个表显式地用括号分组，以便ON子句的操作数是(t1, t2)和t3:

    SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

  • Avoid the use of the comma operator and use JOIN instead: 避免使用逗号操作符，而是使用JOIN:

    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. 同样的优先级解释也适用于将逗号操作符与INNER JOIN、CROSS JOIN、LEFT JOIN和RIGHT JOIN混合使用的语句，这些语句的优先级都高于逗号操作符。

• A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. 与SQL:2003标准相比，MySQL扩展允许您限定NATURAL或USING连接的公共(合并)列，而标准不允许这样做。