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
连接的公共(成对)列,而标准不允许这样做。