数据库外连接及MySQL实现-创新互联
MySQL查询分为内连接查询和外连接查询,他们的区别在于:内连接查询的两个表示对等关系,根据条件进行匹配;外连接是以某一个表为主,两一个表根据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文重点介绍各外连接的思想,以及如何实现全外连接,并举例。
创新互联公司网络公司拥有十多年的成都网站开发建设经验,千余家客户的共同信赖。提供网站设计、成都网站设计、网站开发、网站定制、卖链接、建网站、网站搭建、成都响应式网站建设公司、网页设计师打造企业风格,提供周到的售前咨询和贴心的售后服务左外连接
左外连接以左边表为基础,根据条件,将右边表附属到左边表,语法:SELECT * FROM A LEFT JOIN B ON condition。几何图形关系如下图,即查询结果集除了A表所有数据外,还包含满足条件的B表数据:
右外连接
右外连接以右边表为基础,根据条件,将左边表附属到右边表,语法:SELECT * FROM A RIGHT JOIN B ON condition。几何图形关系如下图,即查询结果集除了B表所有数据外,还包含满足条件的A表数据:
全外连接
全外连接是除了能够根据条件匹配得到的数据,还包含左右两表中都不匹配的数据(默认应为null),应用全外连接的情况一般都有一个联系左右两表的主线。几何关系如下图所示,对应A和B的并集(去重):
但不幸的是MySQL不支持全外连接,那在需要全外连接查询的情况下,如何实现呢?最常见的是左连接与右连接合并。
实例
项目中存在这样的场景:某项任务task具有2种不同的状态todo和done,分别存储在todolist和donelist表中,任务存储在task表中,现在需要统计每个task的已处理和未处理情况。首先先到了全外连接,那么如何实现呢?
举例实现表结构如下:
实现四种方法:
1、左连接,右连接,合并;(需保持两个结果集结构一致)
首先是左连接:
1 SELECT 2 A.id AS Aid, 3 B.id AS Bid, 4 A.taskid tid 5 FROM 6 ( 7 SELECT 8 * 9 FROM10 todolist11 WHERE12 todolist.user = '张三'13 ) A14 LEFT JOIN (15 SELECT16 *17 FROM18 donelist19 WHERE20 donelist.user = '张三'21 ) B ON A.taskid = B.taskid
查询结果:
其次是右连接(注意由于需要合并,故左右连接的结果集结构需一致):
1 SELECT 2 A.id AS Aid, 3 B.id AS Bid, 4 A.taskid tid 5 FROM 6 ( 7 SELECT 8 * 9 FROM10 todolist11 WHERE12 todolist.user = '张三'13 ) A14 RIGHT JOIN (15 SELECT16 *17 FROM18 donelist19 WHERE20 donelist.user = '张三'21 ) B ON A.taskid = B.taskid
查询结果:
最后进行合并,并与task表进行内连接:
1 SELECT 2 SUM(IF(Aid IS NOT NULL, 1, 0)) todo, 3 SUM(IF(Bid IS NOT NULL, 1, 0)) done, 4 task.name 5 FROM 6 ( 7 SELECT 8 A.id AS Aid, 9 B.id AS Bid,10 A.taskid tid11 FROM12 (13 SELECT14 *15 FROM16 todolist17 WHERE18 todolist.user = '张三'19 ) A20 LEFT JOIN (21 SELECT22 *23 FROM24 donelist25 WHERE26 donelist.user = '张三'27 ) B ON A.taskid = B.taskid28 UNION29 SELECT30 A.id AS Aid,31 B.id AS Bid,32 B.taskid tid33 FROM34 (35 SELECT36 *37 FROM38 todolist39 WHERE40 todolist.user = '张三'41 ) A42 RIGHT JOIN (43 SELECT44 *45 FROM46 donelist47 WHERE48 donelist.user = '张三'49 ) B ON A.taskid = B.taskid50 ) AS AB51 INNER JOIN task ON task.id = AB.tid52 GROUP BY53 task.name
运行结果如下表,实现全外连接:
2、A+B左连接,B-A去除左连接到A的记录,然后合并两个结果集;(需保持两个结果集结构一致)
这是另一种实现全外连接的方式,即先查询A B的左连接,然后查询B中去除左连接到A的记录,最后合并(A代表todolist,B代表donelist):
A+B左连接
1 SELECT 2 1 AS todo, 3 CASE 4 WHEN B.id IS NOT NULL THEN 5 1 6 ELSE 7 0 8 END AS done, 9 A.taskid tid 10 FROM11 (12 SELECT13 *14 FROM15 todolist16 WHERE17 todolist.user = '张三'18 ) A19 LEFT JOIN (20 SELECT21 *22 FROM23 donelist24 WHERE25 donelist.user = '张三'26 ) B ON A.taskid = B.taskid
查询结果:
B-A去除左连接到A的记录
1 SELECT 2 0 AS todo, 3 1 AS done, 4 donelist.taskid tid 5 FROM 6 donelist 7 WHERE 8 donelist.user = '张三' 9 AND NOT EXISTS (10 SELECT11 *12 FROM13 todolist14 WHERE15 todolist.taskid = donelist.taskid16 AND donelist.user = '张三'17 AND odolist.user = donelist.user18 )
查询结果:
合并
1 SELECT 2 SUM(AB.todo) todo, 3 SUM(AB.done) done, 4 task.name 5 FROM 6 ( 7 SELECT 8 1 AS todo, 9 CASE10 WHEN B.id IS NOT NULL THEN11 112 ELSE13 014 END AS done,15 A.taskid tid16 FROM17 (18 SELECT19 *20 FROM21 todolist22 WHERE23 todolist.user = '张三'24 ) A25 LEFT JOIN (26 SELECT27 *28 FROM29 donelist30 WHERE31 donelist.user = '张三'32 ) B ON A.taskid = B.taskid33 UNION34 SELECT35 0 AS todo,36 1 AS done,37 donelist.taskid tid38 FROM39 donelist40 WHERE41 donelist.user = '张三'42 AND NOT EXISTS (43 SELECT44 *45 FROM46 todolist47 WHERE48 todolist.taskid = donelist.taskid49 AND donelist.user = '张三'50 AND odolist.user = donelist.user51 )52 ) AB53 INNER JOIN task ON task.id = AB.tid54 GROUP BY55 task.name
结果同上
3、以task表为根本,将A和B表左连接,实现查询;
该方法的思想是,不管A和B表有什么关系,他们都跟作为主线的表task相关,只需要将A和B表与task表进行左连接,得到连接后的数据集,即为最后需要查询的结果集。SQL代码如下:
1 SELECT 2 SUM(AB.todo) AS todo, 3 SUM(AB.done) AS done, 4 task.name 5 FROM 6 ( 7 SELECT 8 task.name, 9 CASE10 WHEN A.id IS NULL THEN11 012 ELSE13 114 END AS todo,15 CASE16 WHEN B.id IS NULL THEN17 018 ELSE19 120 END AS done21 FROM22 task23 LEFT JOIN (24 SELECT25 *26 FROM27 todolist28 WHERE29 todolist.user = '张三'30 ) A ON A.taskid = task.id31 LEFT JOIN (32 SELECT33 *34 FROM35 donelist36 WHERE37 donelist.user = '张三'38 ) B ON B.taskid = task.id39 WHERE40 A.id IS NOT NULL41 OR B.id IS NOT NULL42 ) AB43 GROUP BY44 task.name
查询结果同上,但这种方法存在一定的缺陷,即当主线表(task表)特别大的时候,性能会比较差。
4、A表查a状态,B表查b状态,然后合并;(需保持两个结果集结构一致)
该方法是不管A和B表的关系,现根据条件查询,然后在合并。SQL语句如下:
1 SELECT 2 SUM(A.todo) todo, 3 SUM(A.done) done, 4 task.name 5 FROM 6 ( 7 SELECT 8 1 todo, 9 0 done,10 todolist.taskid tid11 FROM12 todolist13 WHERE14 todolist.user = '张三'15 UNION ALL16 SELECT17 0 todo,18 1 done,19 donelist.taskid tid20 FROM21 donelist22 WHERE23 donelist.user = '张三'24 ) A25 INNER JOIN task ON task.id = A.tid26 GROUP BY27 task.name
查询结果同上。
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
网站名称:数据库外连接及MySQL实现-创新互联
链接地址:http://pwwzsj.com/article/iosso.html