剖析MySQL左连接/右连接/等值连接异同_MySQL

bitsCN.com

从业以来，发现很多技术人员和同事向笔者或是在网络上咨询过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+----+-----------------+
| 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+----+-----------------+------------+--------+
| ID | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 06440350@qq.com |        817 |     39 |
|  2 | 25173782@qq.com |       NULL |   NULL |
|&n bitsCN.com