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 |
+------+------+------+
冗余列的消除和列的排序按照…