select语法详解之join和union

1、：分类：it. JOIN 语法支持以下 JOIN 语法。这些语法用于 SELECT 语句的 table_referen和 UPDATE 语句：部分和多表DELETEtable_referen: table_reference , table_reference table_reference: tabl e_factor | join_table table_factor: tbl_name AS alias USE|IGNORE|FORCE INDEX (key_list) | ( table_referen ) | OJ table_reference LEFT OUTER

2、JOable_reference ON conditional_expr join_table: table_reference INNER | CROSS JO| table_referenable_factor join_conditionTRAIGHT_JOable_factor | table_referenTRAIGHT_JOIN table_factor ON condition | table_reference LEFT OUTERJOJOable_reference join_condition | table_reference NATURAL LEFT OUTER abl

3、e_factor | table_reference RIGHT OUTERJOJOable_reference join_condition | table_reference NATURAL RIGHT OUTER able_factor join_condition: ON conditional_expr | USING (column_list)一个表还被称为一个联合表达式。与 SQL 标准相比，table_factor 的语法被扩展了。SQL 标准只接受 table_reference，而不是圆括号内的一系列条目。如果把一系列 table_reference 条目中的每个逗号都看作

4、相当于一个联合，则这是一个稳妥的扩展。例如：SELECT * FROM t1 LEFT JOIN (t2, t3, t4)ON (t2.a=t1.aAND t3.b=t1.bAND t4.c=t1.c)相当于：SELECT * FROM t1 LEFT JOIN (t2ON (t2.a=t1.aCROSS JO3CROSS JO4)AND t3.b=t1.bAND t4.c=t1.c)在中，CROSS JOIN 从语法上说与 INNER JOIN 等同（两者可以互相替换。在标准 SQL中，两者是不等同的。INNER JOIN 与 ON 子句同时使用，CROSS JOIN 以其它方式使用。通常，

5、在只含有7.2.10 节，“联合运行的联合表达式中，圆括号可以被忽略。 如何优化嵌套 Join”）。也支持嵌套的联合（见通常，您不应对 ON 部分有任何条件。ON 部分用于限定在结果集合中您想要哪些行。但是，您应在WHERE 子句中指定这些条件。这条规则有一些例外。9+2+): 语法详解之 05/4 语法和;4/54 语法(2013-02-2 23:15:27)面的中显示的 OJ . LEFT OUTER JOIN .语法的目的只是为了保持与 ODBC 的兼容性。语法中的花括号应按字面书写；该括号不是中间语法。中间语法用于语法描述的其它地方。表可以使用 tbl_name AS alias_na

6、me 或 tbl_name alias_name 指定别名：ASSELECT, t2.salary FROM employee AS t1, infot2-WHERE = ;, t2.salary FROM employee t1, info t2 WHERE = ;SELECTON 条件句是可以被用于WHERE 子句的格式的任何条件表达式。如果对于在 LEFT JOIN 中的 ON 或 USING 部分中的右表没有匹配的，则所有列被设置为NULL 的一个行被用于右表。如果一个表在其它表中没有对应部分，您可

7、以使用这种方法在这种表中查找：-SELECTtable1.* FROM table1LEFT JOable2 ON table1.id=table2.idWHERE table2.id IS NULL;本例查找在 table1 中含有一个 id 值的所有行。同时，在 table2 中没有此 id 值（即，table1 中的所有行在 table2 中没有对应的行）。本例假设 table2.id 被定义为NOT NULL。请参见 7.2.9 节，“如何优化LEFT JOIN 和 RIGHT JOIN”。USING(column_list)子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。

8、如果表 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 的作用类似。要使代码可以在数据库内移

9、植，建议您使用LEFT JOIN 代替RIGHT JOIN。STRAIGHT_JOIN 与 JOIN 相同。除了有一点不一样，左表会在右表之前被。STRAIGH_JOIN 可以被用于这样的情况，即联合优化符以错误的顺序排列表。您可以提供提示，当从一个表中恢复信息时，应使用哪个索引。通过指定USE INDEX（key_list），您可以告知（key_list）可以被用于告知只使用一个索引来查找表中的行。另一种语法 IGNORE INDEX不要使用某些特定的索引。如果 EXPLAIN 显示正在使用来自索引中的错误索引时，这些提示会有用处。您也可以使用 FORCE INDEX，其作用接近USE IN

10、DEX（key_list），不过增加了一项作用，一次表扫描被假设为代价很高。换句话说，只有当无法使用一个给定的索引来查找表中的行时，才使用表扫描。USE KEY、IGNORE KEY 和 FORCE KEY 是 USE INDEX、IGNORE INDEX 和 FORCE INDEX 的同义词。注释：当决定如何在表中查找行并决定如何进行联合时，使用USE INDEX、IGNOREINDEX 和 FORCE INDEX 只会影响使用哪些索引。当分解一个 ORDER BY 或 GROUP BY 时，这些语句不会影响某个索引是否被使用。部分的联合示例：-SELECT SELECT SELECTSEL

11、ECT*FROM FROM FROM FROM LEFTFROMtable1,table2 WHERE table1.id=table2.id;table1 table1 table1JOLEFTLEFT LEFTJO JO JOONable2able2 able2ON table1.id=table2.id; USING (id);ON table1.id=table2.idable3table2.id=table3.id;SELECT*table1 USE INDEX (key1,key2)WHERE key1=1 AND key2=2 AND key3=3; FROM table1 IG

12、NORE INDEX (key3)WHERE key1=1 AND key2=2 AND key3=3; 如何优化LEFT JOIN 和 RIGHT JOIN”。SELECT*见 7.2.9 节，“注释：自然联合和使用 USING 的联合，包括外部联合变量，依据 SQL:2003 标准被处理。这些变更时与标准 SQL 更加相符。不过，对于有些联合，这些变更会导致不同的输出列。另外，有些查询在旧版本（5.0.12 以前）工作正常，但也必须重新编写，以符合此标准。对于有关当前联合处理和旧版本中的联合处理的效果的对比，以下列表提供了更详细的信息。NATURAL 联合或 USING 联合的列会与旧版本

13、不同。特别是，不再出现冗余的输出列，用于SELECT *扩展的列的顺序会与以前不同。示例：CREATE CREATE INSERT INSERT SELECTSELECTTABLE t1 (iTABLE t2 (k, j, j););O t1 VALUES(1,1); O t2 VALUES(1,1);* FROM t1 NATURAL JO2;* FROM t1 JO2 USING (j);对于旧版本，语句会产生以下输出：+|+|+|+|+|+i|+j|+k|+j|+1|+1|+1|+1+|+i|+j|+k|+j|+1|+1|+1|+1在第一个 SELECT 语句中，列 i 同时出现在两个表

14、中，为一个联合列，所以，依据标准 SQL，该列在输出中只出现一次。与此类似，在第二个 SELECT 语句中，列j 在 USING 子句中被命名，应在输出中只出现一次。但是，在两种情况下，冗余的列均没被消除。另外，依据标准 SQL，列的顺序不正确。现在，语句产生如下输出：+|+j|+i|+k|+|+1|+1|+1|+|+j|+i|+k|+|+1|+1|+1|+冗余的列被消除，并且依据标准 SQL，列的顺序是正确的：第一，两表共有的列，按在第一个表中的顺序排列第二，第一个表中特有的列，按该表中的顺序排列第三，第二个表中特有的列，按该表中的顺序排列对多方式自然联合的估算会不同。方式要求重新编写查询。

15、假设您有三个表 t1(a,b), t2(c,b)和t3(a,c)，每个表有一行：t1(1,2), t2(10,2)和 t3(7,10)。同时，假设这三个表具有NATURAL JOIN：SELECT FROM t1 NATURAL JO2 NATURAL JO3;在旧版本中，第二个联合的左操作数被认为是 t2，然而它应该为嵌套联合（t1 NATURAL JO2）。结果，对 t3 的列进行检查时，只检查其在 t2 中的共有列。如果 t3 与 t1 有共有列，这些列不被用作equi-join 列。因此，在旧版本的SELECT FROM t1, t2, t3中，前面的查询被转换为下面的equi-joi

16、n：WHERE t1.b = t2.b AND t2.c = t3.c;此联合又省略了一个equi-join 谓语（t1.a = t3.a）。结果是，该联合产生一个行，而不是空结果。正确的等价查询如下：SELECT FROM t1, t2, t3WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;如果您要求在当前版本的equi-join。中获得和旧版本中相同的查询结果，应把自然联合改写为第一个在旧版本中，逗号操作符（，）和 JOIN 均有相同的优先权，所以联合表达式 t1, t2 JO3 被理解为(t1, t2) JO3)。现在，JOIN 有更高

17、的优先权，所以表达式被理解为(t1, (t2 JO3)。这个变更会影响使用 ON 子句的语句，因为该子句只参阅联合操作数中的列。优先权的变更改变了对什么是操作数的理解。示例：CREATE CREATE CREATE INSERT INSERT INSERTSELECTTABLE TABLETABLEt1 t2t3(i1(i2(i3,j1 j2j3);););OO Ot1t2 t3VALUES(1,1);VALUES(1,1);VALUES(1,1);* FROM t1, t2 JO3ON (t1.i1 = t3.i3);在旧版本中，SELECT 是合法的，因为 t1, t2 被隐含地归为(t1

18、,t2)。现在，JOIN 取得了优先权，因此用于 ON 子句的操作数是 t2 和 t3。因为 t1.i1 不是任何一个操作数中的列，所以结果是出现在on clause中有未知列t1.i1的错误。要使联合可以被处理，用使用圆括号把前两个表明确地归为一组，这样用于 ON 子句的操作数为(t1,t2)和 t3：SELECT * FROM (t1, t2) JO3 ON (t1.i1 = t3.i3);本变更也适用于 INNER JOIN，CROSS JOIN，LEFT JOIN 和RIGHT JOIN。在旧版本中，ON 子句可以参阅在其右边命名的表中的列。现在，ON 子句只能参阅操作数。示例：CRE

19、ATE TABLE t1 (i1CREATE TABLE t2 (i2););CREATE TABLE t3 (i3SELECT * FROM t1 JO);2 ON (i1 = i3) JO3;在旧版本中，SELECT 语句是合法的。现在该语句会运行失败，出现在on clause中未知列i3的错误。这是因为 i3 是 t3 中的一个表，而 t3 不是 ON 子句中的操作数。本语句应进行如下改写：SELECT * FROM t1 JO2 JO3 ON (i1 = i3);在旧版本中，一个USING 子句可以被改写为一个 ON 子句。ON 子句对比了相应的列。例如，以下两个子句具有相同的语义：a

20、 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 *选择这些值：oCOALESCE(a.c3,b.c3)COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2),对于 ON 联合，SELECT *选

21、择这些值：a.c1, a.c2, a.c3, b.c1, b.c2, b.c3使用联合时，COALESCE(a.c1,b.c1)与a.c1 或b.c1 相同，因为两列将具有相同的值。使用外部联合时（比如 LEFT JOIN），两列中有一列可以为 NULL。该列将会从结果中被忽略。. UNION 语法SELECT .UNION ALL | DISTINCTSELECT UNIONSELECT.ALL | DISTINCT.UNION 用于把来自许多 SELECT 语句的结果组合到一个结果集合中。列于每个 SELECT 语句的对应位置的被选择的列应具有相同的类型。（例如，被第一个语

22、句选择的第一列应和被其它语句选择的第一列具有相同的类型。）在第一个 SELECT 语句中被使用的列名称也被用于结果的列名称。SELECT 语句为常规的选择语句，但是受到如下的限定：只有最后一个 SELECT 语句可以使用O OUTFILE。HIGH_PRIORITY 不能与作为UNION 一部分的 SELECT 语句同时使用。如果您对第一个SELECT 指定了 HIGH_PRIORITY，则不会起作用。如果您对其它后续的 SELECT 语句指定了HIGH_PRIORITY，则会产生语法错误。如果您对UNION 不使用ALL，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了 DISTIN

23、CT。如果您指定了 ALL，您会从所有用过的 SELECT 语句中得到所有匹配的行。是一个自选词，不起任何作用，但是根据 SQL 标准的要求，在语法中允许采用。中，DISTINCT 代表一个共用体的默认工作性质。）DISTINCT（在您可以在同一查询中混合 UNION ALL 和 UNION DISTINCT。被混合的 UNION 类型按照这样的方式对待，即DISTICT 共用体覆盖位于其左边的所有 ALL 共用体。DISTINCT 共用体可以使用 UNIONDISTINCT 明确地生成，或使用 UNION（后面不加DISTINCT 或 ALL）隐含地生成。如果您想使用 ORDER BY 或 LIMIT 子句来对全部 UNION 结果进行分类或限制，则应对单个地SELECT 语句加圆括号，并把 ORDER BY 或 LIMIT 放到最后一个的后面。以下例子同时使用了这两个子句：(SELECT a FROM tbl_name WHERE a=10 AND B=1) UNION(SELECT a FROM tbl_name WHERE a=11 AND B=2)ORDER BY a LIMIT 10;这种 ORDER BY 不能使用包括表名称（也就是，采用 tbl_name.col_name 格式的名称）列。可以在第一个