首页 > 代码库 > 数据库外连接及MySQL实现

数据库外连接及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         FROM
10             todolist
11         WHERE
12             todolist.user = 张三
13     ) A
14 LEFT JOIN (
15     SELECT
16         *
17     FROM
18         donelist
19     WHERE
20         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         FROM
10             todolist
11         WHERE
12             todolist.user = 张三
13     ) A
14 RIGHT JOIN (
15     SELECT
16         *
17     FROM
18         donelist
19     WHERE
20         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 tid
11         FROM
12             (
13                 SELECT
14                     *
15                 FROM
16                     todolist
17                 WHERE
18                     todolist.user = 张三
19             ) A
20         LEFT JOIN (
21             SELECT
22                 *
23             FROM
24                 donelist
25             WHERE
26                 donelist.user = 张三
27         ) B ON A.taskid = B.taskid
28         UNION
29             SELECT
30                 A.id AS Aid,
31                 B.id AS Bid,
32                 B.taskid tid
33             FROM
34                 (
35                     SELECT
36                         *
37                     FROM
38                         todolist
39                     WHERE
40                         todolist.user = 张三
41                 ) A
42             RIGHT JOIN (
43                 SELECT
44                     *
45                 FROM
46                     donelist
47                 WHERE
48                     donelist.user = 张三
49             ) B ON A.taskid = B.taskid
50     ) AS AB
51 INNER JOIN task ON task.id = AB.tid
52 GROUP BY
53     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 FROM
11     (
12         SELECT
13             *
14         FROM
15             todolist
16         WHERE
17             todolist.user = 张三
18     ) A
19 LEFT JOIN (
20     SELECT
21         *
22     FROM
23         donelist
24     WHERE
25         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     SELECT
11         *
12     FROM
13         todolist
14     WHERE
15         todolist.taskid = donelist.taskid
16     AND donelist.user = 张三
17     AND odolist.user = donelist.user
18 )

 查询结果:

技术分享

  • 合并
 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             CASE
10         WHEN B.id IS NOT NULL THEN
11             1
12         ELSE
13             0
14         END AS done,
15         A.taskid tid
16     FROM
17         (
18             SELECT
19                 *
20             FROM
21                 todolist
22             WHERE
23                 todolist.user = 张三
24         ) A
25     LEFT JOIN (
26         SELECT
27             *
28         FROM
29             donelist
30         WHERE
31             donelist.user = 张三
32     ) B ON A.taskid = B.taskid
33     UNION
34         SELECT
35             0 AS todo,
36             1 AS done,
37             donelist.taskid tid
38         FROM
39             donelist
40         WHERE
41             donelist.user = 张三
42         AND NOT EXISTS (
43             SELECT
44                 *
45             FROM
46                 todolist
47             WHERE
48                 todolist.taskid = donelist.taskid
49             AND donelist.user = 张三
50             AND odolist.user = donelist.user
51         )
52     ) AB
53 INNER JOIN task ON task.id = AB.tid
54 GROUP BY
55     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             CASE
10         WHEN A.id IS NULL THEN
11             0
12         ELSE
13             1
14         END AS todo,
15         CASE
16     WHEN B.id IS NULL THEN
17         0
18     ELSE
19         1
20     END AS done
21     FROM
22         task
23     LEFT JOIN (
24         SELECT
25             *
26         FROM
27             todolist
28         WHERE
29             todolist.user = 张三
30     ) A ON A.taskid = task.id
31     LEFT JOIN (
32         SELECT
33             *
34         FROM
35             donelist
36         WHERE
37             donelist.user = 张三
38     ) B ON B.taskid = task.id
39     WHERE
40         A.id IS NOT NULL
41     OR B.id IS NOT NULL
42     ) AB
43 GROUP BY
44     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 tid
11         FROM
12             todolist
13         WHERE
14             todolist.user = 张三
15         UNION ALL
16             SELECT
17                 0 todo,
18                 1 done,
19                 donelist.taskid tid
20             FROM
21                 donelist
22             WHERE
23                 donelist.user = 张三
24     ) A
25 INNER JOIN task ON task.id = A.tid
26 GROUP BY
27     task.name

 查询结果同上。

四种方式只是实现功能,具体性能没有考虑,待后续学习。希望看客们多提意见,共同学习。

总结:

  • 理解左连接、右连接和全外连接的思想
  • 四种实现全外连接效果的方法
  • 语法上注意:UNION可以去重,UNION ALL不去重

 

数据库外连接及MySQL实现