数据库外连接及MySQL实现
MySQL查询分为内连接查询和外连接查询，他们的区别在于：内连接查询的两个表示对等关系，根据条件进行匹配；外连接是以某一个表为主，两一个表根据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文重点介绍各外连接的思想，以及如何实现全外连接，并举例。左外连接
左外连接以左边表为基础，根据条件，将右边表附属到左边表，语法：SELECT * FROM A LEFT JOIN B ON condition。几何图形关系如下图，即查询结果集除了A表所有数据外，还包含满足条件的B表数据：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170628224333086-132628283.png
右外连接
右外连接以右边表为基础，根据条件，将左边表附属到右边表，语法：SELECT * FROM A RIGHT JOIN B ON condition。几何图形关系如下图，即查询结果集除了B表所有数据外，还包含满足条件的A表数据：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170628224543164-910438465.png
全外连接
全外连接是除了能够根据条件匹配得到的数据，还包含左右两表中都不匹配的数据（默认应为null），应用全外连接的情况一般都有一个联系左右两表的主线。几何关系如下图所示，对应A和B的并集（去重）：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170628224914602-1743415611.png
但不幸的是MySQL不支持全外连接，那在需要全外连接查询的情况下，如何实现呢？最常见的是左连接与右连接合并。
实例
项目中存在这样的场景：某项任务task具有2种不同的状态todo和done，分别存储在todolist和donelist表中，任务存储在task表中，现在需要统计每个task的已处理和未处理情况。首先先到了全外连接，那么如何实现呢？
举例实现表结构如下：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629230838039-1382277194.png
实现四种方法：
1、左连接，右连接，合并；（需保持两个结果集结构一致）
[*] 首先是左连接：
http://common.cnblogs.com/images/copycode.gif
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.taskidhttp://common.cnblogs.com/images/copycode.gif
查询结果：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629223422805-130950737.png
[*] 其次是右连接（注意由于需要合并，故左右连接的结果集结构需一致）：
http://common.cnblogs.com/images/copycode.gif
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.taskidhttp://common.cnblogs.com/images/copycode.gif
查询结果：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629223445836-669047825.png
[*] 最后进行合并，并与task表进行内连接：
http://common.cnblogs.com/images/copycode.gif
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.namehttp://common.cnblogs.com/images/copycode.gif
运行结果如下表，实现全外连接：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629222838508-673352268.png
2、A+B左连接，B-A去除左连接到A的记录，然后合并两个结果集；（需保持两个结果集结构一致）
这是另一种实现全外连接的方式，即先查询A B的左连接，然后查询B中去除左连接到A的记录，最后合并（A代表todolist，B代表donelist）：
[*] A+B左连接
http://common.cnblogs.com/images/copycode.gif
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, 9A.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
http://common.cnblogs.com/images/copycode.gif
查询结果：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629230854039-210378164.png
[*] B-A去除左连接到A的记录
http://common.cnblogs.com/images/copycode.gif
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 )http://common.cnblogs.com/images/copycode.gif
查询结果：
http://images2015.cnblogs.com/blog/1012728/201706/1012728-20170629231334086-461751128.png
[*] 合并
http://common.cnblogs.com/images/copycode.gif
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.namehttp://common.cnblogs.com/images/copycode.gif
结果同上
3、以task表为根本，将A和B表左连接，实现查询；
该方法的思想是，不管A和B表有什么关系，他们都跟作为主线的表task相关，只需要将A和B表与task表进行左连接，得到连接后的数据集，即为最后需要查询的结果集。SQL代码如下：
http://common.cnblogs.com/images/copycode.gif
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.namehttp://common.cnblogs.com/images/copycode.gif
查询结果同上，但这种方法存在一定的缺陷，即当主线表（task表）特别大的时候，性能会比较差。
4、A表查a状态，B表查b状态，然后合并；（需保持两个结果集结构一致）
该方法是不管A和B表的关系，现根据条件查询，然后在合并。SQL语句如下：
http://common.cnblogs.com/images/copycode.gif
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.namehttp://common.cnblogs.com/images/copycode.gif
查询结果同上。
页:
[1]