数据库外连接及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

 查询结果同上。