Mysql表连接查询(联结查询)用法及效率分析

1、MySQL左连接、右连接和内连接详解以MySql为例。在MySQL数据库中建立两张数据表，并分别插入一些数据。示例脚本如下：1. droptabletablel;2. CREATETABLE'andrew'.'tablel'3. (4. 'name'VARCHAR32)NOTNULL,5.'city'VARCHAR32)NOTNULL6.)7.ENGINE=MylSAM;8.insertintoTABLE1(name,city)values('PersonA','BJ');9.insertintoT

2、ABLE1(name,city)values('PersonB','BJ');10.insertintoTABLE1(name,city)values('PersonC','SH');11.insertintoTABLE1(name,city)values('PersonD','SZ');12.commit;13.droptabletable2;14.CREATETABLE'andrew'.'table2'15.(16.'name'VARCHAR32)

3、NOTNULL,17.'city'VARCHAR32)NOTNULL18.)19.ENGINE=MylSAM;20.insertintoTABLE2(name,city)values('PersonW,'BJ');21.insertintoTABLE2(name,city)values('PersonX','SH');22.insertintoTABLE2(name,city)values('PersonY','SH');23.insertintoTABLE2(name,city)value

4、s('PersonZ','NJ');24.commit;1.MySQL外连接-左连接结果table1居左，故谓之左连接。这种情况下，以table1为主，即table1中的所有记录均会被列出。有一下三种情况：a.对于tablei中的每一条记录对应的城市如果在table2中也恰好存在而且刚好只有条，那么就会在返回的结果中形成一条新的记录。如上面PersonA和PersonB对应的情况。table2中也恰好存在而且有N条，那PersonC对应的情况。b,对于tablel中的每一条记录对应的城市如果在么就会在返回的结果中形成N条新的记录。如上面的c,对于tablel中的

5、每一条记录对应的城市如果在table2中不存在，那么就会在返回的结果中形成一条条新的记录，且该记录的右边全部NULL。如上面的PersonD对应的情况。不符合上面三条规则的记录不会被列出。2. MySQL外连接-右连接结果table2居右，故谓之右连接。这种情况下，以table2为主，即table2中的所有记录均会被列出。有一下三种情况：a,对于table2中的每一条记录对应的城市如果在tablel中也恰好存在而且刚好只有一条，那么就会在返回的结果中形成一条新的记录。如上面PersonX和PersonY对应的情况。tablel中也恰好存在而且有N条，那b,对于table2中的每一条记录对应的城

6、市如果在么就会在返回的结果中形成N条新的记录。如上面的PersonW对应的情况。tablel中不存在，那么就会在返回的c,对于table2中的每一条记录对应的城市如果在结果中形成一条条新的记录，且该记录的左边全部NULL。如上面的PersonZ对应的情况。不符合上面三条规则的记录不会被列出。3. MySQL内连接MySQL内连接的数据记录中，不会存在字段为NULL的情况。可以简单地认为，内链接的结果就是在左连接或者右连接的结果中剔除存在字段为NULL的记录后所得到的结果。甚至可以认为，如果两个表中仅分别剩下内连接运算后所得的数据记录，如tablel中只有PersonA、PersonB和Pers

7、onC,table2中只有PersonW、PersonX和PersonY,那么这两个表的之间的左连接和右连接的返回的结果是一样的。注意：select*fromtablelainnerjointable2bona,city=b.city和select*fromtablelajointable2bona,city=b.city的效果是一样的，即如果join的左边没有诸如left、right或者innerMySQL的联结（Join）语法1 .内联结、外联结、左联结、右联结的含义及区别:在讲MySQLBJoin语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内

8、容有错误或有疑问，可以来信咨询：陈朋奕chenpengyi#）,国内关于MySQLM结查询的资料十分少，相信大家在看了本文后会对MySQLM结语法有相当清晰的了解，也不会被Oracle的外联结的（“+”号）弄得糊涂了。在SQL标准中规划的（Join）联结大致分为下面四种：1 .内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。2 .外联结：分为外左联结和外右联结。左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。右联结A、B表的结果和左联结B、A的结果

9、是一样的，也就是说：SelectA.nameB.nameFromALeftJoinBOnA.id=B.id和SelectA.nameB.nameFromBRightJoinAonB.id=A.id执行后的结果是一样的。3,全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。4.无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则

10、A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版社出版的数据库系统概论书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。2.MySQLB（Join）的语法MySQ或持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有:table_references:table_reference,table_reference,table_reference:table_factor|join_tabletable_factor:tbl_nameASaliasUSE

11、|IGNORE|FORCEINDEX(key_list)|(table_references)|OJtable_referenceLEFTOUTERJOINtable_referenceONconditional_exprjoin_table:table_referenceINNER|CROSSJOINtable_factorjoin_condition|table_referenceSTRAIGHT_JOINtable_factor|table_referenceSTRAIGHT_JOINtable_factorONcondition|table_referenceLEFTOUTERJOIN

12、table_referencejoin_condition|table_referenceNATURALLEFTOUTERJOINtable_factor|table_referenceRIGHTOUTERJOINtable_referencejoin_condition|table_referenceNATURALRIGHTOUTERJOINtable_factorjoin_condition:ONconditional_expr|USING(column_list)上面的用法摘自权威资料，不过大家看了是否有点晕呢？呵呵，应该问题主要还在于table_reference是什么，table_f

13、actor又是什么？这里的table_reference其实就是表的引用的意思，因为在MySQL看来，联结就是一种对表的引用，因此把需要联结的表定义为table_reference,同时在SQLStandard中也是如此看待的。而table_factor则是MySQL寸这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表，如下面例子中的JOIN后面括号：SELECT*FROMt1LEFTJOIN(t2,t3,t4)ON(t2.a=t1.aANDt3.b=t1.bANDt4.c=t1,c)这个语句的执行结果和下面语句其实是一样的：SELECT*FROMt1LEFTJOIN(t2CRO

14、SSJOINt3CROSSJOINt4)ON(t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c)这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSJOIN的用法，我要补充的是在MySQLB有版本中CROSJOIN的作用和INNERJOIN是一样的(虽然在SQLStandard中是不一样的，然而在MySQL中他们的区别仅仅是INNERJOIN需要附加ON参数的语句，而CROSSJOIN不需要)。既然说到了ON语句，那就解释一下吧，ON语句其实和WHER第句功能大致相当，只是这里的ON语句是专门针对联结表的，

15、ON吾句后面的条件的要求和书写方式和WHER题句的要求是一样的，大家基本上可以把ON当作WHERE!。大家也许也看到了OJtable_referenceLEFTOUTERJOINtable_reference这个句子，这不是MySQL的标准写法，只是为了和ODBC勺SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。那下面就具体讲讲简单的JOIN的用法了。首先我们假设有2个表A和B,他们的表结构和字段分别为：表A:IDName1Tim2Jimmy3John4Tom表B:IDHobby1Football2Basketball2Tennis4Soccer1 .内联结:,这是

16、隐式的内联结，查询的结果是:SelectA.NameB.HobbyfromA,BwhereA.id=B.idNameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer它的作用和SelectA.NamefromAINNERJOINBONA.id=B.id是一样的。这里的INNERJOIN换成CROSSJOIN也是可以的2 .外左联结,典型的外左联结，这样查询得到的结果SelectA.NamefromALeftJOINBONA.id=B.id将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下:NameHobbyT

17、imFootballJimmyBasketball,TennisJohnTomSoccer所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID,因此为空，但Name栏仍有John记录。3 .外右联结如果把上面查询改成外右联结：SelectA.NamefromARightJOINBONA.id=B.id将会是:NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer这样的结果都是我们可以从外左联结的结果中猜到的了说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧(呵呵，开玩笑了)?最后给

18、大家讲讲MySQL联结查询中的某些参数的作用：1. USING(column_list):其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：aLEFTJOINbUSING(c1,c2,c3),其作用相当于下面语句aLEFTJOINbONa.c1=b.c1ANDa.c2=b.c2ANDa.c3=b.c3只是用ON来代替会书写比较麻烦而已。2. NATURALLEFTJOIN:这个句子的作用相当于INNERJOIN,或者是在USING子句中包含了联结的表中所有字段的LeftJOIN(左联结)。3. STRAIGHT_JOIN由于默认情况下MySQ小进

19、行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL勺内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：SELECTt1.id,t2.id,t3.idFROMt1,t2LEFTJOINt3ON(t3.id=t1.id)WHEREt1.id=t2.id;但是MySQ用不是这样执行的，其后台的真正执行方式是下面的语句：SELECTt1.id,t2.

20、id,t3.idFROMt1,(t2LEFTJOINt3ON(t3.id=t1.id)WHEREt1.id=t2.id;这并不是我们想要的效果，所以我们需要这样输入：SELECTt1.id,t2.id,t3.idFROM(t1,t2)LEFTJOINt3ON(t3.id=t1.id)WHEREt1.id=t2.id;在这里括号是相当重要的，因此以后在写这样的查询的时候我们不要忘记了多写几个括号，至少这样能避免很多错误(因为这样的错误是很难被开发人员发现的)。如果对上面内容有疑问可以来信查询：陈朋奕chenpengyi#,转载请注明出处及作者。MySQLleftjoin联合查询的效率分析2010

21、-05-2114:36佚名博客园我要评论(0) 摘要：我们今天主要向大家介绍的是MySQLleftjoin联合查询的效率分析，以及在实际操作中值得我们大家注意的事项的描述。 标签：MySQLleftjoin以下的文章主要讲述的是MySQLleftjoin联合查询的效率分析，我在一个信誉度很好的网站找到一个关于MySQLleftjoin联合查询的效率分析的资料，今天拿出来供大家分享,希望会给你带来一些帮助在此方面。user表：代码：id|name1 |libk2 |zyfon3|daodaouseraction表:代码：user_id|action1 |jump2 |kick3 |jump4 |

22、run5 |swimsql:代码：selectid,name,actionfromuserasuleftjoinuser_actionaonu.id=a.user_idresult:代码：id|name|action1 |libk|jump2 |libk|kick3 |libk|jump4 |zyfon|run5 |daodao|null分析：注意到user_action中还有一个user_id=4,action=swim的纪录，但是没有在结果中出现，而user表中的id=3,name=daodao的用户在user_action中没有相应的纪录，但是却出现在了结果集中因为现在是MySQLlef

23、tjoin，所有的工作以left为准.结果1,2,3,4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录结论：我们可以想象MySQLleftjoin是这样工作的从左表读出一条，选出所有与on匹配的右表纪录（n条）进行连接，形成n条纪录（包括重复的行，如：结果1和结果3）,如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条。引申：我们可以用右表没有on匹配则显示null的规律，来找出所有在左表，不在右表的纪录，注意用来判断的那列必须声明为notnull的。如：sql:代码：1. selectid,name,actionfromuserasu2. leftjoinu

24、ser_actionaonu.id=a.user_id3. wherea.user_idisNULL4. 意：1.列值为null应该用isnull而不能用=NULL5. 这里a.user_id列必须声明为NOTNULL的）代码：1. result:2. id|name|action3. 3|daodao|NULLTips:1. ona.c1=b.c1等同于using（c1）2. INNERJOIN和，（逗号）在语义上是等同的3. 当MySQL在从一个表中检索信息时，你可以提示它选择了哪一个索引。如果EXPLAIN显示MySQL使用了可能的索引列表中错误的索引，这个特性将是很有用的。通过指定US

25、EINDEX（key_list），你可以告诉MySQL使用可能的索引中最合适的一个索引在表中查找记录行。可选的二选一句法IGNOREINDEX(key_list)可被用于告诉MySQL不使用特定的索引。4. 一些例子：代码：1. MySQL>SELECT*FROMtable1,table2WHEREtablel.id=table2.id;2. MySQL>SELECT*FROMtableiLEFTJOINtable2ONtablel.id=table2.id;3. MySQL>SELECT*FROMtableiLEFTJOINtable2USING(id);4. MySQL&

26、gt;SELECT*FROMtableiLEFTJOINtable2ONtablel.id=table2.id5. ->LEFTJOINtable3ONtable2.id=table3.id;6. MySQL>SELECT*FROMtableiUSEINDEX(key1,key2)7. ->WHEREkeyi=1ANDkey2=2ANDkey3=3;8. MySQL>SELECT*FROMtableiIGNOREINDEX(key3)9. ->WHEREkeyi=iANDkey2=2ANDkey3=3;7.2.9. MySQL如何优化LEFTJOIN和RIGHTJOIN在MySQL中，ALEFTJOINBjoin_condition执行过程如下：根据表A和A依赖的所有表设置表Bo根据MySQLLEFTJOIN条件中使用的所有表(除了B)设置表A。LEFTJOIN条件用于确定如何从表B搜索彳To(换句话说，不使用WHERE子句中的任何条件)。可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如