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