mysql join语法解析

MySQL 支持以下 JOIN 语法用于 SELECT 语句和多表 DELETE 和 UPDATE 语句中的 table_references 部分：

table_references:

查询中涉及的一个或多个表的引用，可以是简单表名或 JOIN 表达式的组合。

escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference:

带有特定语法的表引用，可能包含外部连接（OUTER JOIN）的标记。

{ 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:

由两个或多个表通过 JOIN 语法组合而成的表。

{
  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_hint 中使用。

index_name [, index_name] ...

其他说明：

表引用也称为连接表达式。当表引用指向分区表时，可以包含 PARTITION 子句，包括用逗号分隔的分区、子分区或两者。这个选项跟在表名之后，位于任何别名声明之前。这个选项的效果是仅从列出的分区或子分区中选择行。未在列表中命名的分区或子分区将被忽略。

与标准 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 还支持嵌套连接。

可以指定索引提示，以影响 MySQL 优化器如何使用索引。优化器提示和 optimizer_switch 系统变量是影响优化器使用索引的其他方式。

编写连接时需要考虑的常见因素：

可以使用 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 子句中的子查询。这样的子查询必须包含别名，以为子查询结果提供表名，并可以选择性地在括号中包含列名列表。以下是一个简单示例：

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

在单个连接中可以引用的表的最大数量为 61。这包括通过将派生表和视图合并到外部查询块中处理的连接。

在没有连接条件的情况下，INNER JOIN 和逗号（,）在语义上是等价的：两者在指定表之间产生笛卡尔积（即，第一个表中的每一行都与第二个表中的每一行连接）。

然而，逗号运算符的优先级低于 INNER JOIN、CROSS JOIN、LEFT JOIN 等。如果在有连接条件的情况下将逗号连接与其他连接类型混合使用，则可能会发生“Unknown column 'col_name' in 'on clause'”错误。

有关处理此问题的信息将在本节后面提供。

注意事项：

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

如果在 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 中具有 id 值但在 right_tbl 中不存在的行（即，所有在 left_tbl 中没有对应行的行）。

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

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

两个表的 NATURAL [LEFT] JOIN 定义为在语义上等价于 INNER JOIN 或 LEFT JOIN，使用列出所有在两个表中都存在的列的 USING 子句。

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

冗余列的消除和列的排序按照...