MySQL支持part of
语句和multiple-table and
语句的JOIN
语法如下:
table_references
SELECT
DELETE
UPDATE
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] ...
表引用也称为连接表达式。
表引用(当它指的是分区表时)可能包含一个PARTITION
子句,包括以逗号分隔的分区、子分区或两者的列表。此选项跟在表的名称之后并在任何别名声明之前。此选项的效果是仅从列出的分区或子分区中选择行。任何未在列表中命名的分区或子分区都将被忽略。有关更多信息和示例,请参阅
第 19.5 节,“分区选择”。
与标准 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 还支持嵌套连接。请参阅第 8.2.1.7 节,“嵌套连接优化”。
可以指定索引提示来影响 MySQL 优化器如何使用索引。有关详细信息,请参阅
第 8.9.3 节,“索引提示”。optimizer_switch
系统变量是影响优化器使用索引的另一种方式。
请参阅
第 8.9.2 节,“可切换优化”。
以下列表描述了编写连接时要考虑的一般因素:
可以使用 or 为表引用起别名:
tbl_name
ASalias_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
中也称为派生表或子查询。FROM
请参阅第 13.2.10.8 节,“派生表”。这样的子查询必须包含一个别名来给子查询结果一个表名。一个简单的例子如下:SELECT * FROM (SELECT 1, 2, 3) AS t1;
单个连接中可以引用的最大表数为 61。
INNER JOIN
和,
(逗号)在没有连接条件的情况下在语义上是等价的:两者都在指定的表之间产生笛卡尔积(也就是说,第一个表中的每一行都连接到第二个表中的每一行)。但是,逗号运算符的优先级低于 ,
INNER JOIN
,CROSS JOIN
,LEFT JOIN
等。如果在存在连接条件时将逗号连接与其他连接类型混合使用,则可能会出现格式错误。本节后面将提供有关处理此问题的信息。Unknown column '
col_name
' in 'on clause'search_condition
used withON
是可以在WHERE
子句中使用的形式 的任何条件表达式。通常,该ON
子句用于指定如何连接表的条件,并且该WHERE
子句限制要包含在结果集中的行。ON
如果 a或USING
a 中的部分 右表没有匹配行,则将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
的值的所有行(即,在 中没有对应行的 所有行)。请参阅 第 8.2.1.8 节,“外部连接优化”。id
right_tbl
left_tbl
right_tbl
该 子句命名必须存在于两个表中的列列表。如果表和 都包含列 、和 ,则以下连接比较两个表中的相应列:
USING(
join_column_list
)a
b
c1
c2
c3
a LEFT JOIN b USING (c1, c2, c3)
NATURAL [LEFT] JOIN
of two tables 被定义为在语义上等同于 anINNER JOIN
或 aLEFT JOIN
with aUSING
clause that names all the columns exist in both tables .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;
自然连接和连接 with 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
条语句中,columnj
出现在两个表中,因此成为一个连接列,因此,根据标准 SQL,它应该在输出中只出现一次,而不是两次。类似地,在第二个 SELECT 语句中,列j
在子句中命名USING
并且在输出中应该只出现一次,而不是两次。因此,这些语句会产生以下输出:
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
冗余列消除和列排序根据标准 SQL 发生,产生此显示顺序:
首先,合并两个连接表的公共列,按照它们在第一个表中出现的顺序
其次,第一个表独有的列,按照它们在该表中出现的顺序
第三,第二个表特有的列,按照它们在该表中出现的顺序
替换两个公共列的单个结果列是使用合并操作定义的。也就是说,对于两个
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)
如果连接操作是任何其他连接,则连接的结果列由连接表的所有列的串联组成。
合并列定义的结果是,对于外部联接,
NULL
如果两列之一始终为 ,则合并列包含非列的值NULL
。如果两个列都不是NULL
,则两个公共列都具有相同的值,因此选择哪个列作为合并列的值并不重要。解释这一点的一种简单方法是考虑外部联接的合并列由 a 的内部表的公共列表示JOIN
。假设表t1(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 | +------+------+------+
相比之下,对于此连接,column
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
选择所有表中的所有列。对于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
对于内部联接,与 one or
COALESCE(a.c1, b.c1)
相同, 因为两列具有相同的值。使用外连接(例如),两列之一可以是。结果中省略了该列。a.c1
b.c1
LEFT JOIN
NULL
ON
子句只能引用其操作数 。例子:
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
比逗号运算符 ( ) 具有更高的优先级,
,因此连接表达式t1, t2 JOIN t3
被解释为(t1, (t2 JOIN t3))
,而不是((t1, t2) JOIN t3)
。这会影响使用ON
子句的语句,因为该子句只能引用连接操作数中的列,并且优先级会影响对这些操作数的解释。例子:
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
优先于逗号运算符,因此ON
子句的操作数是t2
andt3
。因为t1.i1
不是任何一个操作数中的列,所以结果是Unknown column 't1.i1' in 'on clause'
错误的。要使连接能够被处理,请使用以下任一策略:
将前两个表明确地用括号分组,以便该
ON
子句的操作数是(t1, t2)
andt3
: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
orUSING
连接的公共(合并)列,而标准不允许这样做。