n 三种连接的语法
为便于更多的技友快速读懂、理解,我们只讨论2张表对象进行连接操作的情况,大于2张表对象进行的连接操作原理也是一样的。
u 左连接(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…..
u 右连接(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…..
u 等值连接
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语法中加粗的红色字体部分,三种不同连接语法的变化。
n 三种连接的特性
u 左连接(LEFT JOIN )
l ON子句连接条件,用于把2表中等值的记录连接在一起,但是不影响记录集的数量。若是表left_table中的某记录,无法在表right_table找到对应的记录,则此记录依然显示在记录集中,只是表 right_table需要在查询显示的列的值用NULL替代;
l ON子句连接条件中表right_table.columnname=XXX用于控制right_table表是否有符合要求的列值还是用NULL替换的方式显示在查询列中,不影响记录集的数量;
l WHERE子句控制记录是否符合查询要求,不符合则过滤掉;
总结:
ON子句控制right_table的列值符合显示,还是不符合就用NULL替换,不影响最终符合查询要求的记录集;WHERE子句是控制那些记录是显示在最终的记录集中。
u 右连接(RIGHT JOIN)
l ON子句连接条件,用于把2表中等值的记录连接在一起,若是表right_table中的某记录,无法在表left_table找到对应的记录,则表 left_able需要在查询显示的列的值用NULL替代;
l ON子句连接条件中表left_table. columnname=XXX用于控制left_table表是否有符合要求的列值,还是用NULL替换的方式显示在查询列表中;
l WHERE子句控制记录是否符合查询要求,不符合则过滤掉;
总结:
ON子句控制left_table的列值符合显示,还是不符合而用NULL替换掉,不影响最终符合查询要求的记录集;WHERE子句是控制那些记录是显示在最终的记录集中。我们会发现LEFT JOIN 和RIGHT JOIN是类似的,只是以连接关键字左边还是右边表为准匹配。
u 等值连接
l ON 子句连接条件,不再与左连接或右连接的功效一样,除了作为2表记录匹配的条件外,还会起到过滤记录的作用,若left_table中记录无法在right_table中找到对应的记录,则会被过滤掉;
l WHERE子句,不管是涉及表left_table、表right_table上的限制条件,还是涉及2表连接的条件,都会对记录集起到过滤作用,把不符合要求的记录刷选掉;
n 连接语法的测试环境
u 测试用表结构
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’;
u 用于测试的数据
重复执行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实现;
n 对比测试
l 基准测试表中将可能看到的数据(编号: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 |
+—-+—————–+
u 标准左连接
l 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 |
+—-+—————–+————+——–+
l 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 |
+—-+—————–+————+——–+
u 带JOIN关键字 的等值连接
l 对应编号: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 |
+—-+—————–+————+——–+
l 对应编号: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 |
+—-+—————–+————+——–+
u 不带JOIN关键字的等值连接
l 对应编号: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 |
+—-+—————–+————+——–+
l 对应编号: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 |
+—-+—————–+————+——–+
u 常见错误用法的左连接
l 错把限制表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 |
+—-+—————–+————+——–+
l 错把限制表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 |
+—-+—————–+————+——–+
u 测试总结
l 编号SQL_1
表left_table真实参与测试的记录的ID值:1,2,3,4,5,6共计6条连续的记录;
l 编号SQL_2、SQL_3
对于LEFT JOIN 连接操作,增加right_table的限制条件,不影响最后记录集的数量,但是影响right_table表中需要显示出来的列值,不符合限制条件的情况下,则用NULL值替代。
l 编号SQL_4、SQL_5
对于等值连接操作,ON子句中增加对right_table的限制条件,会对最终的记录集数量产生影响;
l 编号SQL_6、SQL_7
对于等值连接操作,WHERE子句中增加对right_table的限制条件,会对最终的记录集数量产生影响;
l 编号(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之间,为此部再单独描述;
l 编号(SQL_4、SQL_6)或(SQL_5、SQL_7)
对于等值连接操作,不管对right_table的限制条件,放ON子句,还是WHERE子句中,对记录集影响都是一样的;
l 编号SQL_3、SQL_5、SQL_8
SQL_5与SQL_3相比较我们可发现,用的连接关键字都是:左连接,但是ON子句中的表right_table限制条件被放到WHERE子句中,导致最后获得的记录集完全不一样,而是跟等值连接SQL_8的记录集一样,通过比较可以发现SQL_5的记录集不符合左连接的特性,而是符合等值连接的特性,所以此写法不是左连接语句,而是相当于等值连接的语句;
l 编号SQL_2、SQL_3、SQL_9
SQl_9与SQL_2、SQL_3的连接关键字都是:左连接,其记录集相比较可以发现限制表left_table的记录条件:M.ID<=6没有起作用,限制表right_table的记录条件:N.SearchNum>300依然有效果,从左连接的特性角度分析,依然属于左连接,只是写法很混乱,容易误导SQL的读者;
后续:
从业以来,发现很多技友或同事向在下或者网络上咨询过LEFT JOIN 如何写及其特性,而等值连接一般都没问题,几年前曾经写过一份Microsoft SQL Server版本的,此次特意再写一份MySQL版本的,希望阅读过的技友们,可以起到解惑功效,那将是我收到的最好礼物(转贴请标注出处,及作者英文名称:Eugene)。
[整理自网络]
分类目录
- arch/management (17)
- computer (38)
- java/j2ee (304)
- lnmpa (237)
- mac/iphone/ipad/android (11)
- mysql/oracle/postgresql (126)
- os/software (74)
- other (518)
- python (6)
- redis/memcached/mongo (31)
- sitebuild (143)
随便看看
标签云
程序员 创业 人生箴言 eclipse 快捷键 术语 索引 unix命令 vim wordpress java学习笔记 环境变量 oracle内置函数 index 人生 数据类型 nohup tuxedo mysql学习笔记 MS-DOS命令 servlet spring 职场进阶 职业进阶 服务器选购 服务器选型 apache JPA MongoDB 注解 tomcat 子女教育 jquery maven JVM aix命令 网络营销 java异常 seo 人生规划 关键字 css 网络推广 struts 系统优化 成长 frame iframe bluehost jdbc select 我的信仰 oracle函数 cookie HashMap 站长工具 乱码 ArrayList secureCRT jsp session tail find halt 事务 oracle单记录函数 算法 URL window table javascript操作表单元素 String 字符串处理 健康 http 域名 情感 more google A记录 域名解析 netstat 弹出对话框 弹出窗口 框架集 框架 excel 字符串 javascript函数 showModalDialog nginx number 数组 sql frameset 开源程序 java数组 软件 oracle服务友情链接
收藏链接