<b>解析MySQL左衔接,右衔接,等值衔接异同</b>[MySQL防范]

从业以来,发现很多技术人员和同事向笔者或是在网络上咨询过LEFT JOIN 若何写及其特点,而等值衔接普通都没问题,几年前曾经写过一份Microsoft SQL Server版本的,此次特地再写一份MySQL版本的,但愿阅读过的技术人员,可以起到解惑功效.

　　1、三种衔接的语法

　　为便于更多的技友快速读懂、理解,我们只谈论2张表对象举行衔接操作的情形,大于2张表对象举行的衔接操作原理也是一样的.

　　左衔接(LEFT JOIN )

SELECT M.columnname……,N.* columnname…..
FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX
WHERE M.columnname=XXX…..
 　　右衔接(RIGHT JOIN)

SELECT M.columnname……,N.* columnname…..
FROM left_table M RIGHT JOIN right_table N ON M. columnname_join=N. columnname_join AND M. columnname=XXX
WHERE N.columnname=XXX…..
 　　等值衔接

SELECT M.columnname……,N.* columnname…..
FROM left_table M [INNER] JOIN right_table N ON M. columnname_join=N. columnname_join
WHERE M.columnname=XXX….. AND N.columnname=XXX….
大概
SELECT M.columnname……,N.* columnname…..
FROM left_table M  ,  right_table N
WHERE M. columnname_join=N. columnname_join  AND　
M.columnname=XXX….. AND N.columnname=XXX….
 　　备注:注意上面SQL语法中加粗的红色字体部份,三种差别衔接语法的改变.

　　三种衔接的特点

　　左衔接(LEFT JOIN )

　　ON字句衔接条件,用于把2表中等值的记录衔接在一同,但是不影响记录集的数目.若是表left_table中的某记录,无法在表right_table找到对应的记录,则此记录仍然显示在记录集钟,只表 right_table需求在查询显示的列的值用NULL替换;

　　ON字句衔接条件中表right_table. columnname=XXX用于掌握right_table表能否有符合要求的列值还是用NULL替换的方法显示在查询列中,不影响记录集的数目;

　　WHERE字句掌握记录能否符合查询要求,不符合则过滤掉;

　　总结:ON字句掌握right_table的列值符合显示,还是不符合就用NULL替换,不影响终究符合查询要求的记录集;WHERE字句是掌握那些记录是显示在终究的记录集合.

　　右衔接(RIGHT JOIN)

　　ON字句衔接条件,用于把2表中等值的记录衔接在一同,若是表right_table中的某记录,无法在表left_table找到对应的记录,则表 left_able需求在查询显示的列的值用NULL替换;

　　ON字句衔接条件中表left_table. columnname=XXX用于掌握left_table表能否有符合要求的列值,还是用NULL替换的方法显示在查询列表中;

　　WHERE字句掌握记录能否符合查询要求,不符合则过滤掉;

　　总结:ON字句掌握left_table的列值符合显示,还是不符合而用NULL替换掉,不影响终究符合查询要求的记录集;WHERE字句是掌握那些记录是显示在终究的记录集合.我们会发现LEFT JOIN 和RIGHT JOIN是近似的,只是以衔接关键字左边还是右边表为准匹配.

　　等值衔接

　　ON 字句衔接条件,不再与左衔接或右衔接的功效一样,除了作为2表记录匹配的条件外,还会起到过滤记录的作用,若left_table中记录无法在right_table中找到对应的记录,则会被过滤掉;

　　WHERE字句,不管是触及表left_table、表right_table上的限制条件,还是触及2表衔接的条件,城市对记录集起到过滤作用,把不符合要求的记录刷选掉;

　　衔接语法的测试环境

　　测试用表构造

CREATE TABLE left_table(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    Username VARCHAR(40) NOT NULL,
                    Birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
                    CityID     SMALLINT NOT NULL DEFAULT 0,
               CreatDate  TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
               AlterDate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        PRIMARY KEY(ID),
                        KEY idx_username(Username)
                )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE right_table(UID INT UNSIGNED NOT NULL ,
                CollectNum MEDIUMINT NOT NULL DEFAULT 0,
                BuyNum     MEDIUMINT NOT NULL DEFAULT 0,
                SearchNum  MEDIUMINT NOT NULL DEFAULT 0,
                CreatDate  TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
               AlterDate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
               PRIMARY KEY(UID)
                   )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
　　用于测试的数据

　　反复履行10次INSERT*** VALUE ***语句:

    INSERT INTO left_table(Username,Birthday,CityID,CreatDate,AlterDate)
VALUES(CONCAT(SUBSTRING(RAND(),3,8),'@qq.com'),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) YEAR),SUBSTRING(RAND(),3,2),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY));
 　　履行一次INSERT ** SELECT **语句:

    INSERT INTO right_table
SELECT ID,SUBSTRING(RAND(),3,4) AS CollectNum,SUBSTRING(RAND(),3,2) AS BuyNum,SUBSTRING(RAND(),3,3) AS SearchNum,CreatDate,AlterDate
FROM left_table WHERE ID%5=1;
 　　注释:表left_table将会有ID值1,2,3,***,10持续的记录10条,表right_table中有ID值1,6离散的记录2条,并且2表关联条件为:left_table.ID=right_table.UID实现.

　　比较测试

　　基准测试表中将大概看到的数据(编号:SQL_1)

root@localhost : eugene 03:25:07> SELECT M.ID,M.username FROM left_table M WHERE M.ID<=6;
+----+-----------------+
| ID | username        |
+----+-----------------+
|  1 | 06440350@qq.com |
|  2 | 25173782@qq.com |
|  3 | 66328120@qq.com |
|  4 | 16752438@qq.com |
|  5 | 92117196@qq.com |
|  6 | 02026078@qq.com |
    +----+-----------------+
　　尺度左衔接

　　ON字句中无衔接字段之外条件的SQL及数据(编号:SQL_2)

root@localhost : eugene 03:37:58> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID
-> WHERE M.ID<=6;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
|  2 | 25173782@qq.com |       NULL |   NULL |
|  3 | 66328120@qq.com |       NULL |   NULL |
|  4 | 16752438@qq.com |       NULL |   NULL |
|  5 | 92117196@qq.com |       NULL |   NULL |
|  6 | 02026078@qq.com |       5177 |     36 |
    +----+-----------------+------------+--------+
　　ON字句中除衔接字段条件之外,还有right_table限制条件的SQL及数据(编号:SQL_3)

root@localhost : eugene 03:40:20> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID  AND N.SearchNum>300
    -> WHERE M.ID<=6;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
|  2 | 25173782@qq.com |       NULL |   NULL |
|  3 | 66328120@qq.com |       NULL |   NULL |
|  4 | 16752438@qq.com |       NULL |   NULL |
|  5 | 92117196@qq.com |       NULL |   NULL |
|  6 | 02026078@qq.com |       NULL |   NULL |
    +----+-----------------+------------+--------+
　　带JOIN关键字的等值衔接

　　对应编号:SQL_2的等值衔接测试的SQL及数据(编号:SQL_4)

root@localhost : eugene 03:41:27> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M INNER JOIN right_table N ON M.ID=N.UID
-> WHERE M.ID<=6;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
|  6 | 02026078@qq.com |       5177 |     36 |
    +----+-----------------+------------+--------+
　　对应编号:SQL_3的等值衔接测试的SQL及数据(编号:SQL_5)

root@localhost : eugene 03:42:53> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M INNER JOIN right_table N ON M.ID=N.UID  AND N.SearchNum>300
-> WHERE M.ID<=6;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
    +----+-----------------+------------+--------+
　　不带JOIN关键字的等值衔接

　　对应编号:SQL_4的等值衔接测试的SQL及数据(编号:SQL_6)

root@localhost : eugene 03:43:01> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M,right_table N
-> WHERE M.ID=N.UID  AND M.ID<=6;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
|  6 | 02026078@qq.com |       5177 |     36 |
    +----+-----------------+------------+--------+
　　对应编号:SQL_5的等值衔接测试的SQL及数据(编号:SQL_7)

root@localhost : eugene 03:49:35> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M,right_table N
-> WHERE M.ID=N.UID  AND  M.ID<=6 AND N.SearchNum>300;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
    +----+-----------------+------------+--------+
　　常见错误用法的左衔接

　　错把限制表right_table的条件,从ON 子句中放到WHERE字句中的SQL及数据(编号:SQL_8)

root@localhost : eugene 03:49:57> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID 
-> WHERE M.ID<=6 AND N.SearchNum>300;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
    +----+-----------------+------------+--------+
　　错把限制表left_table或称影响终究记录集的条件,从WHERE子句中放到ON字句中的SQL及数据(编号:SQL_9)

root@localhost : eugene 03:54:14> SELECT M.ID,M.username,N.CollectNum,N.BuyNum
-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID  AND M.ID<=6 AND  N.SearchNum>300;
+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  6 | 02026078@qq.com |       NULL |   NULL |
|  7 | 03990516@qq.com |       NULL |   NULL |
|  9 | 05301926@qq.com |       NULL |   NULL |
|  1 | 06440350@qq.com |        817 |     39 |
|  4 | 16752438@qq.com |       NULL |   NULL |
|  2 | 25173782@qq.com |       NULL |   NULL |
| 10 | 56599949@qq.com |       NULL |   NULL |
|  3 | 66328120@qq.com |       NULL |   NULL |
|  5 | 92117196@qq.com |       NULL |   NULL |
|  8 | 93677961@qq.com |       NULL |   NULL |
    +----+-----------------+------------+--------+

　　测试总结

　　编号SQL_1:表left_table真实参与测试的记录的ID值:1,2,3,4,5,6总计6条持续的记录;

　　编号SQL_2、SQL_3:关于LEFT JOIN 衔接操作,增添right_table的限制条件,不影响最后记录集的数目,但是影响right_table表中需求显示出来的列值,不符合限制条件的情形下,则用NULL值替换;

　　编号SQL_4、SQL_5:关于等值衔接操作,ON字句中增添对right_table的限制条件,会对终究的记录集数目产生影响;

　　编号SQL_6、SQL_7:关于等值衔接操作,WHERE字句中增添对right_table的限制条件,会对终究的记录集数目产生影响;

　　编号(SQL_2、SQL_4)或(SQL_3、SQL_5):我们可以简单发现编号:SQL_2、SQL_4 之间唯一的差别是衔接的范例差别:SQL_2为左衔接,而SQL_4为等值衔接,其他全部的都一样,然后SQL履行后得到的记录集却截然不一样,并且SQL_2是对符合表left_table限制条件的记录都作为最后的记录集,而SQL_4则是对同时符合表left_table、表right_table限制条件的记录才作为最后的记录集;SQL_2与SQL_4之间比较得到的结论,一样实用于SQL_3与SQL_5之间,为此部再单独描写;

　　编号(SQL_4、SQL_6)或(SQL_5、SQL_7):关于等值衔接操作,不管对right_table的限制条件,放ON字句,还是WHERE子句中,对记录集影响都是一样的;

　　编号SQL_3、SQL_5、SQL_8:SQL_5与SQL_3相对比我们可发现,用的衔接关键字都是:左衔接,但是ON子句中的表right_table限制条件被放到WHERE子句中,招致最后得到的记录集完好不一样,而是跟等值衔接SQL_8的记录集一样,通过对比可以发现SQL_5的记录集不符合左衔接的特点,而是符合等值衔接的特点,所以此写法不是左衔接语句,而是相当于等值衔接的语句;

　　编号SQL_2、SQL_3、SQL_9:SQl_9与SQL_2、SQL_3的衔接关键字都是:左衔接,其记录集相对比可以发现限制表left_table的记录条件:M.ID<=6没有起作用,限制表right_table的记录条件:N.SearchNum>300仍然有效果,从左衔接的特点角度解析,仍然属于左衔接,只是写法很混乱,简单误导SQL的读者. 　　以上是“<b>解析MySQL左衔接,右衔接,等值衔接异同</b>[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：