您好,登录后才能下订单哦!
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 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
运行结果如下表,实现全外连接:
这是另一种实现全外连接的方式,即先查询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
结果同上
该方法的思想是,不管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表)特别大的时候,性能会比较差。
该方法是不管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
查询结果同上。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。