MySQL的SQL语句 - 数据操作语句(12)- SELECT 语句(3)

JOIN 子句
MySQL 对 SELECT 语句和多表 DELETE 和 UPDATE 语句 table_references 部分支持以下 JOIN 语法:

1. table_references:
2.     escaped_table_reference [, escaped_table_reference] ...
3. 
4. escaped_table_reference: {
5.     table_reference
6.   | { OJ table_reference }
7.    }
8. 
9. table_reference: {
10.     table_factor
11.   | joined_table
12. }
13. 
14. table_factor: {
15.     tbl_name [PARTITION (partition_names)]
16.         [[AS] alias] [index_hint_list]
17.   | [LATERAL] table_subquery [AS] alias [(col_list)]
18.   | ( table_references )
19. }
20. 
21. joined_table: {
22.     table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
23.   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
24.   | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
25. }
26. 
27. join_specification: {
28.     ON search_condition
29.   | USING (join_column_list)
30. }
31. 
32. join_column_list:
33.     column_name [, column_name] ...
34. 
35. index_hint_list:
36.     index_hint [, index_hint] ...
37. 
38. index_hint: {
39.     USE {INDEX|KEY}
40.       [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
41.   | {IGNORE|FORCE} {INDEX|KEY}
42.       [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
43. }
44. 
45. index_list:
46.     index_name [, index_name] ...

表引用也称为联接表达式。

表引用(当它引用分区表时)可以包含 PARTITION 选项,包括逗号分隔的分区、子分区列表。此选项紧跟在表名之后,并位于任何别名之前。这个选项的效果是只从列出的分区或子分区中选择行。忽略列表中未命名的任何分区或子分区。

与标准 SQL 相比,MySQL 扩展了 table_factor 的语法。标准 SQL 只接受 table_reference,而不接受一对括号内的列表。

如果 table_reference 项目列表中的每个逗号都被视为等同于内部联接,则这是一个保守的扩展。例如:

1. SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
2.                  ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

等价于:

1. SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
2.                  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 优化器如何使用索引。

下表描述了写联接语句时要考虑的一般因素:

● 可以使用 tbl_name AS alias_name 或 tbl_name alias_name 对表引用使用别名:

1. SELECT t1.name, t2.salary
2.   FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
3. 
4. SELECT t1.name, t2.salary
5.   FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

● table_subquery 在 FROM 子句中也称为派生表或子查询。此类子查询必须包含别名,以便为子查询结果提供表名,并且可以选择在括号中包含表的列名。下面是一个简单的例子:

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

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

● 在没有连接条件的情况下,INNER JOIN 和 ,(逗号)在语义上是等价的:两者都在指定的表之间生成笛卡尔积(也就是说,第一个表中的每一行都连接到第二个表中的每一行)。

但是,逗号运算符的优先级低于 INNER JOIN、CROSS JOIN、LEFT JOIN 等。如果在存在连接条件时将逗号联接与其他联接类型混合使用,则可能会出现类似于 Unknown column ‘col_name‘ in ‘on clause‘ 的错误。有关处理此问题的信息将在本节后面部分给出。

● 与 ON 一起使用的 search_condition 是可以在 WHERE 子句中使用的条件表达式。通常,ON 子句用于指定如何联接表的条件,WHERE 子句限制要在结果集中包括哪些行。

● 如果 LEFT JOIN 中的 ON 或 USING 部分中的右表没有匹配的行,则查出的右表该行将所有列都设置为 NULL。可以使用此事实来查找表中在另一个表中没有对应项的行:

1. SELECT left_tbl.*
2.   FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
3.   WHERE right_tbl.id IS NULL; 

此示例查找 left_tbl 中 id 值不在 right_tbl 中的所有行(也就是说,left_tbl 中的所有行在 right butl 都中没有对应的行)。

● USING(join_column_list) 子句指定两个表中必须存在的列名的列表。如果表a和表b都包含列c1、c2和c3,则下面的联接将比较两个表中的相应列:

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

● 两个表的 NATURAL [LEFT] JOIN 被定义为语义上等价于 INNER JOIN 或带有 USING 子句的 LEFT JOIN,USING 子句命名两个表中存在的所有列。

● RIGHT JOIN 的工作原理与 LEFT JOIN 类似。为了保持代码在数据库之间的可移植性,建议使用 LEFT JOIN 而不是 RIGHT JOIN。

● 联接语法描述中显示的 { OJ ... } 语法仅用于与 ODBC 兼容。语法中的大括号应该按字面意思写;它们不是语法描述中其他地方使用的元语法。

1. SELECT left_tbl.*
2.     FROM { OJ left_tbl LEFT OUTER JOIN right_tbl
3.            ON left_tbl.id = right_tbl.id }
4.     WHERE right_tbl.id IS NULL;

可以在 { OJ ... } 中使用其他类型的连接,例如 INNER JOIN 或 RIGHT OUTER JOIN。这有助于与某些第三方应用程序兼容,但不是官方的 ODBC 语法。

● STRAIGHT_JOIN 类似于 JOIN,只是左表总是在右表之前读取。这可以用于连接优化器以次优顺序处理表的那些(少数)情况。

一些连接示例:

1. SELECT * FROM table1, table2;
2. 
3. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
4. 
5. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
6. 
7. SELECT * FROM table1 LEFT JOIN table2 USING (id);
8. 
9. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
10.   LEFT JOIN table3 ON table2.id = table3.id;

自然联接和使用 USING 的联接,包括外部联接变体,根据 SQL:2003 标准处理:

● NATURAL 联接的冗余列不会出现。参考这组语句:

1. CREATE TABLE t1 (i INT, j INT);
2. CREATE TABLE t2 (k INT, j INT);
3. INSERT INTO t1 VALUES(1, 1);
4. INSERT INTO t2 VALUES(1, 1);
5. SELECT * FROM t1 NATURAL JOIN t2;
6. SELECT * FROM t1 JOIN t2 USING (j);

在第一个 SELECT 语句中,j 列出现在两个表中,因此成为一个联接列,因此,根据标准 SQL,它应该只在输出中出现一次,而不是两次。类似地,在第二个 SELECT 语句中,列 j 在 USING 子句中命名,并且应该只在输出中出现一次,而不是两次。

因此,这些语句产生以下输出:

1. +------+------+------+
2. | j    | i    | k    |
3. +------+------+------+
4. |    1 |    1 |    1 |
5. +------+------+------+
6. +------+------+------+
7. | j    | i    | k    |
8. +------+------+------+
9. |    1 |    1 |    1 |
10. +------+------+------+

根据标准 SQL 进行冗余列消除和列排序,生成以下显示顺序:

■ 首先,按照它们在第一个表中出现的顺序,合并两个连接表的公共列

■ 第二,按照它们在该表中出现的顺序排列第一个表所特有的列

■ 第三,按照它们在该表中出现的顺序排列第二个表所特有的列

替换两个公共列,获得单个结果列是使用合并操作定义的。也就是说,对于两个 t1.a 和 t2.a,生成的单个联接列 a 定义为 a = COALESCE(t1.a, t2.a),其中:

1. COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)

如果联接操作是任何其他联接,则联接的结果列由联接表的所有列的组成。

合并列定义的结果是,对于外部联接,如果两个列中的一个始终为 NULL,则合并列包含非 NULL 列的值。如果两列都为 NULL 或都不为 NULL,则两个公共列都具有相同的值,因此选择哪一列作为合并列的值无关紧要。解释这种情况的一种简单方法是,考虑外部联接的合并列由联接的内部表的公共列表示。假设表 t1(a, b) 和 t2(a, c) 具有以下内容:

1.t1    t2
2.----  ----
3.1 x   2 z
4.2 y   3 w

然后,对于这个连接,a 列包含 t1.a 的值:

1.mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
2. +------+------+------+
3. | a    | b    | c    |
4. +------+------+------+
5. |    1 | x    | NULL |
6. |    2 | y    | z    |
7. +------+------+------+

相比之下,对于这个连接,列 a 包含 t2.a 的值。


1. 1. mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2 ON (t1.a = t2.a);
2. +------+------+------+------+
3. | a    | b    | a    | c    |
4. +------+------+------+------+
5. |    1 | x    | NULL | NULL |
6. |    2 | y    |    2 | z    |
7. +------+------+------+------+
1.mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
2.+------+------+------+------+
3.| a    | b    | a    | c    |
4.+------+------+------+------+
5.|    2 | y    |    2 | z    |
6.| NULL | NULL |    3 | w    |
7.+------+------+------+------+

● USING 子句可以重写为比较相应列的 ON 子句。然而,尽管 USING 和 ON 是相似的,但它们并不完全相同。考虑以下两个查询:

1. a LEFT JOIN b USING (c1, c2, c3)
2.a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

关于确定哪些行满足联接条件,两个联接在语义上是相同的。

关于确定要为 SELECT 显示哪些列,这两个联接在语义上是不相同的。USING 联接选择相应列的合并值,而 ON 联接选择所有表中的所有列。对于 USING 联接,SELECT 选择以下值:

1.COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), 2.COALESCE(a.c3, b.c3)

对于 ON 联接,SELECT * 选择以下值:

1.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 子句只能引用其操作数。

例子:

1.CREATE TABLE t1 (i1 INT);
2.CREATE TABLE t2 (i2 INT);
3.CREATE TABLE t3 (i3 INT);
4.SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

语句失败并出现 Unknown column ‘i3‘ in ‘on clause‘ 错误,因为 i3 是 t3 中的列,而 t3 不是 ON 子句的操作数。要使联接能够被处理,请重写语句,如下所示:

1.SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

● JOIN 的优先级高于逗号运算符 (,),因此连接表达式 t1, t2 JOIN t3 解释为 (t1, (t2 JOIN t3)),而不是 ((t1, t2) JOIN t3)。这会影响使用 ON 子句的语句,因为该子句只能引用联接操作数中的列,优先级会影响对这些操作数的解析。

例子:

1.CREATE TABLE t1 (i1 INT, j1 INT);
2. CREATE TABLE t2 (i2 INT, j2 INT);
3. CREATE TABLE t3 (i3 INT, j3 INT);
4. INSERT INTO t1 VALUES(1, 1);
5. INSERT INTO t2 VALUES(1, 1);
6. INSERT INTO t3 VALUES(1, 1);
7. SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

JOIN 优先于逗号运算符,因此 ON 子句的操作数是 t2 和 t3。因为 t1.i1 在两个操作数中都不是列,因此结果会报错:Unknown column ‘t1.i1‘ in ‘on clause‘。

要使联接能够被处理,请使用以下任一策略:

■ 将前两个表用括号显式分组,以便 ON 子句的操作数是 (t1, t2) 和 t3:

1. SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

■ 避免使用逗号运算符,改用 JOIN:

1. SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

同样的优先级解释也适用于将逗号运算符与 INNER JOIN、CROSS JOIN、LEFT JOIN 和 RIGHT JOIN 混合使用的语句,这些语句的优先级都高于逗号运算符。

● MySQL 扩展了 SQL:2003 标准,允许限定自然联接或 USING 联接的公共(合并)列,而标准不允许这样做。

官方网址:
https://dev.mysql.com/doc/refman/8.0/en/join.html

MySQL的SQL语句 - 数据操作语句(12)- SELECT 语句(3)

上一篇:网页中嵌入swf文件的几种方法


下一篇:MySQL的SQL语句 - 数据操作语句(13)- 子查询(6)