数据库外连接及MySQL实现

发布时间:2020-07-11 15:35:34 作者:lzwxx
来源:网络 阅读:285

MySQL查询分为内连接查询和外连接查询,他们的区别在于:内连接查询的两个表示对等关系,根据条件进行匹配;外连接是以某一个表为主,两一个表根据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文重点介绍各外连接的思想,以及如何实现全外连接,并举例。

左外连接

左外连接以左边表为基础,根据条件,将右边表附属到左边表,语法:SELECT * FROM A LEFT JOIN B ON condition。几何图形关系如下图,即查询结果集除了A表所有数据外,还包含满足条件的B表数据:

数据库外连接及MySQL实现

右外连接

右外连接以右边表为基础,根据条件,将左边表附属到右边表,语法:SELECT * FROM A RIGHT JOIN B ON condition。几何图形关系如下图,即查询结果集除了B表所有数据外,还包含满足条件的A表数据:

数据库外连接及MySQL实现

全外连接

全外连接是除了能够根据条件匹配得到的数据,还包含左右两表中都不匹配的数据(默认应为null),应用全外连接的情况一般都有一个联系左右两表的主线。几何关系如下图所示,对应A和B的并集(去重):

数据库外连接及MySQL实现

但不幸的是MySQL不支持全外连接,那在需要全外连接查询的情况下,如何实现呢?最常见的是左连接与右连接合并。

实例

项目中存在这样的场景:某项任务task具有2种不同的状态todo和done,分别存储在todolist和donelist表中,任务存储在task表中,现在需要统计每个task的已处理和未处理情况。首先先到了全外连接,那么如何实现呢?

举例实现表结构如下:

数据库外连接及MySQL实现

实现四种方法:

1、左连接,右连接,合并;(需保持两个结果集结构一致)

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 运行结果如下表,实现全外连接:

数据库外连接及MySQL实现

2、A+B左连接,B-A去除左连接到A的记录,然后合并两个结果集;(需保持两个结果集结构一致)

这是另一种实现全外连接的方式,即先查询A B的左连接,然后查询B中去除左连接到A的记录,最后合并(A代表todolist,B代表donelist):

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 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 )

数据库外连接及MySQL实现

 查询结果:

数据库外连接及MySQL实现

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 结果同上

3、以task表为根本,将A和B表左连接,实现查询;

该方法的思想是,不管A和B表有什么关系,他们都跟作为主线的表task相关,只需要将A和B表与task表进行左连接,得到连接后的数据集,即为最后需要查询的结果集。SQL代码如下:

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 查询结果同上,但这种方法存在一定的缺陷,即当主线表(task表)特别大的时候,性能会比较差。

4、A表查a状态,B表查b状态,然后合并;(需保持两个结果集结构一致)

该方法是不管A和B表的关系,现根据条件查询,然后在合并。SQL语句如下:

数据库外连接及MySQL实现

 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

数据库外连接及MySQL实现

 查询结果同上。



推荐阅读:
  1. 举例介绍mysql外连接
  2. 什么是mysql全外连接

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 外连

上一篇:apache如何实现重启?

下一篇:需要安装apache上的ab吗?

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》