mysql对比等值连接_技术专题MySQL的左连接、右连接、等值连接异同

现象

XXX项目中使用左联接的SQL语句，几乎全部存在如图1-1所示的不当写法：

图1-1

根据此现象，今天分享一下三种连接的语法、属性及测试对比等内容，欢迎大家一起交流。

三种连接的语法

为便于更多的技友快速读懂、理解，只讨论2张表对象进行连接操作的情况，大于2张表对象进行的连接操作原理也是一样的。左连接 (LETFJOIN)

SELECT M.columnname……,N.* columnname…..

FROM left_table MLEFT JOINright_table NONM. columnname_join=N. columnname_joinAND N. columnname=XXX

WHERE M.columnname=XXX…..

右连接(RIGHT JOIN)SELECT M.columnname……,N.* columnname…..

FROM left_table MRIGHT JOINright_table NONM. columnname_join=N. columnname_joinAND M. columnname=XXX

WHERE N.columnname=XXX…..

等值连接

SELECT M.columnname……,N.* columnname…..

FROM left_table M[INNER] JOINright_table NONM. 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

WHEREM. columnname_join=N. columnname_joinAND

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 CUR RENT_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 WHEREID%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

Tag标签: