MySQL 5.1参考手册 :: 13. SQL语句语法(3)

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

table_references: table_reference [, table_reference] … table_reference: table_factor | join_table table_factor: tbl_name [[AS] alias] [{USE|IGNORE|FORCE} INDEX (key_list)] | ( table_references ) | { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr } join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON condition | table_reference LEFT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor | table_reference RIGHT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor join_condition: ON conditional_expr | USING (column_list) 

一个表引用还被称为一个联合表达式。

与SQL标准相比，table_factor的语法被扩展了。SQL标准只接受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中，CROSS JOIN从语法上说与INNER JOIN等同（两者可以互相替换。在标准SQL中，两者是不等同的。INNER JOIN与ON子句同时使用，CROSS JOIN以其它方式使用。

通常，在只含有内部联合运行的联合表达式中，圆括号可以被忽略。MySQL也支持嵌套的联合（见7.2.10节，“MySQL如何优化嵌套Join”）。

通常，您不应对ON部分有任何条件。ON部分用于限定在结果集合中您想要哪些行。但是，您应在WHERE子句中指定这些条件。这条规则有一些例外。

在前面的清单中显示的{ OJ ... LEFT OUTER JOIN ...}语法的目的只是为了保持与ODBC的兼容性。语法中的花括号应按字面书写；该括号不是中间语法。中间语法用于语法描述的其它地方。

·         表引用可以使用tbl_name AS alias_name或tbl_name alias_name指定别名：

·                mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2

·                    ->        WHERE t1.name = t2.name;

·                mysql> SELECT t1.name, t2.salary FROM employee t1, info t2

·                    ->        WHERE t1.name = t2.name;

·         ON条件句是可以被用于WHERE子句的格式的任何条件表达式。

·         如果对于在LEFT JOIN中的ON或USING部分中的右表没有匹配的记录，则所有列被设置为NULL的一个行被用于右表。如果一个表在其它表中没有对应部分，您可以使用这种方法在这种表中查找记录：

·                mysql> SELECT table1.* FROM table1

·                    ->        LEFT JOIN table2 ON table1.id=table2.id

·                    ->        WHERE table2.id IS NULL;

本例查找在table1中含有一个id值的所有行。同时，在table2中没有此id值（即，table1中的所有行在table2中没有对应的行）。本例假设table2.id被定义为NOT NULL。请参见7.2.9节，“MySQL如何优化LEFT JOIN和RIGHT JOIN”。

·         USING(column_list)子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。如果表a和表b都包含列c1, c2和c3，则以下联合会对比来自两个表的对应的列：

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

·         两个表的NATURAL [LEFT] JOIN被定义为与INNER JOIN语义相同，或与使用USING子句的LEFT JOIN语义相同。USING子句用于为同时存在于两个表中的所有列进行命名。

·         INNER JOIN和，（逗号）在无联合条件下是语义相同的：两者都可以对指定的表计算出笛卡儿乘积（也就是说，第一个表中的每一行被联合到第二个表中的每一行）。

·         RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，建议您使用LEFT JOIN代替RIGHT JOIN。

·         STRAIGHT_JOIN与JOIN相同。除了有一点不一样，左表会在右表之前被读取。STRAIGH_JOIN可以被用于这样的情况，即联合优化符以错误的顺序排列表。

您可以提供提示，当从一个表中恢复信息时，MySQL应使用哪个索引。通过指定USE INDEX（key_list），您可以告知MySQL只使用一个索引来查找表中的行。另一种语法IGNORE INDEX（key_list）可以被用于告知MySQL不要使用某些特定的索引。如果EXPLAIN显示MySQL正在使用来自索引清单中的错误索引时，这些提示会有用处。

您也可以使用FORCE INDEX，其作用接近USE INDEX（key_list），不过增加了一项作用，一次表扫描被假设为代价很高。换句话说，只有当无法使用一个给定的索引来查找表中的行时，才使用表扫描。

USE KEY、IGNORE KEY和FORCE KEY是USE INDEX、IGNORE INDEX和FORCE INDEX的同义词。

注释：当MySQL决定如何在表中查找行并决定如何进行联合时，使用USE INDEX、IGNORE INDEX和FORCE INDEX只会影响使用哪些索引。当分解一个ORDER BY或GROUP BY时，这些语句不会影响某个索引是否被使用。

部分的联合示例：

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

    ->          LEFT JOIN table3 ON table2.id=table3.id;

mysql> SELECT * FROM table1 USE INDEX (key1,key2)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

mysql> SELECT * FROM table1 IGNORE INDEX (key3)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

见7.2.9节，“MySQL如何优化LEFT JOIN和RIGHT JOIN”。

注释：自然联合和使用USING的联合，包括外部联合变量，依据SQL:2003标准被处理。这些变更时MySQL与标准SQL更加相符。不过，对于有些联合，这些变更会导致不同的输出列。另外，有些查询在旧版本（5.0.12以前）工作正常，但也必须重新编写，以符合此标准。对于有关当前联合处理和旧版本中的联合处理的效果的对比，以下列表提供了更详细的信息。

·         NATURAL联合或USING联合的列会与旧版本不同。特别是，不再出现冗余的输出列，用于SELECT *扩展的列的顺序会与以前不同。

示例：

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);

对于旧版本，语句会产生以下输出：

+------+------+------+------+

| i    | j    | k    | j    |

+------+------+------+------+

|    1 |    1 |    1 |    1 |

+------+------+------+------+

+------+------+------+------+

| i    | j    | k    | j    |

+------+------+------+------+

|    1 |    1 |    1 |    1 |

+------+------+------+------+

在第一个SELECT语句中，列i同时出现在两个表中，为一个联合列，所以，依据标准SQL，该列在输出中只出现一次。与此类似，在第二个SELECT语句中，列j在USING子句中被命名，应在输出中只出现一次。但是，在两种情况下，冗余的列均没被消除。另外，依据标准SQL，列的顺序不正确。

现在，语句产生如下输出：

+------+------+------+

| j    | i    | k    |

+------+------+------+

|    1 |    1 |    1 |

+------+------+------+

+------+------+------+

| j    | i    | k    |

+------+------+------+

|    1 |    1 |    1 |

+------+------+------+

冗余的列被消除，并且依据标准SQL，列的顺序是正确的：

o        第一，两表共有的列，按在第一个表中的顺序排列

o        第二，第一个表中特有的列，按该表中的顺序排列

o        第三，第二个表中特有的列，按该表中的顺序排列

·         对多方式自然联合的估算会不同。方式要求重新编写查询。假设您有三个表t1(a,b), t2(c,b)和t3(a,c)，每个表有一行：t1(1,2), t2(10,2)和t3(7,10)。同时，假设这三个表具有NATURAL JOIN：

·                SELECT … FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

在旧版本中，第二个联合的左操作数被认为是t2，然而它应该为嵌套联合（t1 NATURAL JOIN t2）。结果，对t3的列进行检查时，只检查其在t2中的共有列。如果t3与t1有共有列，这些列不被用作equi-join列。因此，在旧版本的MySQL中，前面的查询被转换为下面的equi-join：

SELECT … FROM t1, t2, t3

  WHERE t1.b = t2.b AND t2.c = t3.c;

此联合又省略了一个equi-join谓语（t1.a = t3.a）。结果是，该联合产生一个行，而不是空结果。正确的等价查询如下：

SELECT … FROM t1, t2, t3

  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;

如果您要求在当前版本的MySQL中获得和旧版本中相同的查询结果，应把自然联合改写为第一个equi-join。

·         在旧版本中，逗号操作符（，）和JOIN均有相同的优先权，所以联合表达式t1, t2 JOIN t3被理解为((t1, t2) JOIN t3)。现在，JOIN有更高的优先权，所以表达式被理解为(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);

在旧版本中，SELECT是合法的，因为t1, t2被隐含地归为(t1,t2)。现在，JOIN取得了优先权，因此用于ON子句的操作数是t2和t3。因为t1.i1不是任何一个操作数中的列，所以结果是出现在'on clause'中有未知列't1.i1'的错误。要使联合可以被处理，用使用圆括号把前两个表明确地归为一组，这样用于ON子句的操作数为(t1,t2)和t3：

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

本变更也适用于INNER JOIN，CROSS JOIN，LEFT JOIN和RIGHT JOIN。

·         在旧版本中，ON子句可以参阅在其右边命名的表中的列。现在，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;

在旧版本中，SELECT语句是合法的。现在该语句会运行失败，出现在'on clause'中未知列'i3'的错误。这是因为i3是t3中的一个表，而t3不是ON子句中的操作数。本语句应进行如下改写：

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

·         在旧版本中，一个USING子句可以被改写为一个ON子句。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

现在，这两个子句不再是一样的：

o        在决定哪些行满足联合条件时，两个联合保持语义相同。

o        在决定哪些列显示SELECT *扩展时，两个联合的语义不相同。USING联合选择对应列中的合并值，而ON联合选择所有表中的所有列。对于前面的USING联合，SELECT *选择这些值：

o                     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。该列将会从结果中被忽略。