Mysql连接用法及效率分析

MySQL左连接、右连接和内连接详解以MySql为例。在MySQL数据库中建立两张数据表，并分别插入一些数据。示例脚本如下：1.droptabletable1;2.CREATETABLE`andrew`.`table1`3.(4.`name`VARCHAR(32)NOTNULL,5.`city`VARCHAR(32)NOTNULL6.)7.ENGINE=MyISAM;8.insertintoTABLE1(name,city)values('PersonA','BJ');9.insertintoTABLE1(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`VARCHAR(32)NOTNULL,17.`city`VARCHAR(32)NOTNULL18.)19.ENGINE=MyISAM;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)values('PersonZ','NJ');24.commit;1.MySQL外连接–左连接结果table1居左，故谓之左连接。这种情况下，以table1为主，即table1中的所有记录均会被列出。有一下三种情况：a.对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且刚好只有一条，那么就会在返回的结果中形成一条新的记录。如上面PersonA和PersonB对应的情况。b.对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。如上面的PersonC对应的情况。c.对于table1中的每一条记录对应的城市如果在table2中不存在，那么就会在返回的结果中形成一条条新的记录，且该记录的右边全部NULL。如上面的PersonD对应的情况。不符合上面三条规则的记录不会被列出。2.MySQL外连接–右连接结果table2居右，故谓之右连接。这种情况下，以table2为主，即table2中的所有记录均会被列出。有一下三种情况：a.对于table2中的每一条记录对应的城市如果在table1中也恰好存在而且刚好只有一条，那么就会在返回的结果中形成一条新的记录。如上面PersonX和PersonY对应的情况。b.对于table2中的每一条记录对应的城市如果在table1中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。如上面的PersonW对应的情况。c.对于table2中的每一条记录对应的城市如果在table1中不存在，那么就会在返回的结果中形成一条条新的记录，且该记录的左边全部NULL。如上面的PersonZ对应的情况。不符合上面三条规则的记录不会被列出。3.MySQL内连接MySQL内连接的数据记录中，不会存在字段为NULL的情况。可以简单地认为，内链接的结果就是在左连接或者右连接的结果中剔除存在字段为NULL的记录后所得到的结果。甚至可以认为，如果两个表中仅分别剩下内连接运算后所得的数据记录，如table1中只有PersonA、PersonB和PersonC，table2中只有PersonW、PersonX和PersonY，那么这两个表的之间的左连接和右连接的返回的结果是一样的。注意：select*fromtable1ainnerjointable2bona.city=b.city和select*fromtable1ajointable2bona.city=b.city的效果是一样的，即如果join的左边没有诸如left、right或者inner这样的关键字时，缺省的是内连接。另外，MySQL不支持fulljoin。MySQL的联结（Join）语法1．内联结、外联结、左联结、右联结的含义及区别：在讲MySQL的Join语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内容有错误或有疑问，可以来信咨询：陈朋奕chenpengyi#gmail.com），国内关于MySQL联结查询的资料十分少，相信大家在看了本文后会对MySQL联结语法有相当清晰的了解，也不会被Oracle的外联结的（“＋”号）弄得糊涂了。在SQL标准中规划的（Join）联结大致分为下面四种：1．内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。2．外联结：分为外左联结和外右联结。左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：SelectA.nameB.nameFromALeftJoinBOnA.id=B.id和SelectA.nameB.nameFromBRightJoinAonB.id=A.id执行后的结果是一样的。3．全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。4．无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版社出版的《数据库系统概论》书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。2．MySQL联结（Join）的语法MySQL支持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有：table_references:table_reference[,table_reference]…table_reference:table_factor|join_tabletable_factor:tbl_name[[AS]alias][{USE|IGNORE|FORCE}INDEX(key_list)]|(table_references)|{OJtable_referenceLEFTOUTERJOINtable_referenceONconditional_expr}join_table:table_reference[INNER|CROSS]JOINtable_factor[join_condition]|table_referenceSTRAIGHT_JOINtable_factor|table_referenceSTRAIGHT_JOINtable_factorONcondition|table_referenceLEFT[OUTER]JOINtable_referencejoin_condition|table_referenceNATURAL[LEFT[OUTER]]JOINtable_factor|table_referenceRIGHT[OUTER]JOINtable_referencejoin_condition|table_referenceNATURAL[RIGHT[OUTER]]JOINtable_factorjoin_condition:ONconditional_expr|USING(column_list)上面的用法摘自权威资料，不过大家看了是否有点晕呢？呵呵，应该问题主要还在于table_reference是什么，table_factor又是什么？这里的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(t2CROSSJOINt3CROSSJOINt4)ON(t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c)这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSSJOIN的用法，我要补充的是在MySQL现有版本中CROSSJOIN的作用和INNERJOIN是一样的（虽然在SQLStandard中是不一样的，然而在MySQL中他们的区别仅仅是INNERJOIN需要附加ON参数的语句，而CROSSJOIN不需要）。既然说到了ON语句，那就解释一下吧，ON语句其实和WHERE语句功能大致相当，只是这里的ON语句是专门针对联结表的，ON语句后面的条件的要求和书写方式和WHERE语句的要求是一样的，大家基本上可以把ON当作WHERE用。大家也许也看到了OJtable_referenceLEFTOUTERJOINtable_reference这个句子，这不是MySQL的标准写法，只是为了和ODBC的SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。那下面就具体讲讲简单的JOIN的用法了。首先我们假设有2个表A和B，他们的表结构和字段分别为：表A：IDName1Tim2Jimmy3John4Tom表B：IDHobby1Football2Basketball2Tennis4Soccer1．内联结：SelectA.NameB.HobbyfromA,BwhereA.id=B.id，这是隐式的内联结，查询的结果是：NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer它的作用和SelectA.NamefromAINNERJOINBONA.id=B.id是一样的。这里的INNERJOIN换成CROSSJOIN也是可以的。2．外左联结SelectA.NamefromALeftJOINBONA.id=B.id，典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：NameHobbyTimFootballJimmyBasketball，TennisJohnTomSoccer所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。3．外右联结如果把上面查询改成外右联结：SelectA.NamefromARightJOINBONA.id=B.id，则结果将会是：NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer这样的结果都是我们可以从外左联结的结果中猜到的了。说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MyS