合并 UNION

UNION 是将介绍的三种集合运算中最常用的运算,用于求两个 SELECT 语句结果的交集。

1
{主 SELECT 语句} UNION {副 SELECT 语句};

需要注意的是,UNION 的使用中 (INTERSECTEXCEPT 也一样) 要求且只要求两个 SELECT 语句输出的字段数相同,也就是说,如果两个 SELECT 语句返回的类型不同,依然可以使用 UNION。即使两个数据表毫无关系,只要字段数相同,都可以使用 UNION

基本使用

1
2
3
4
5
6
7
8
9
10
SELECT 
starName AS name
FROM
StarsIn
UNION
SELECT
name
FROM
MovieExec;
-- 合并 StarsIn 和 MovieExec 中的人名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+-------------------+
| name |
+-------------------+
| Harrison Ford |
| Carrie Fisher |
| Mark Hamill |
| Debra Winger |
| Jack Nicholson |
| Kevin Spacey |
| Huangjianxin |
| Calvin Coolidge |
| Merv Griffin |
| Stephen Spielberg |
| Ted Turner |
| George Lucas |
| Jane Fonda |
+-------------------+

UNION 和 UNION ALL

除了 UNION 关键字外还有 UNION ALL 关键字,两者的区别在于,UNION 关键字默认会进行去重操作,而 UNION ALL 不会。下面两个操作是等效的。

1
SELECT DISTINCT * FROM ({主 SELECT 语句} UNION ALL {副 SELECT 语句}) AS A;
1
{主 SELECT 语句} UNION {副 SELECT 语句};
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
*
FROM
Movies
WHERE
movieType="feature"
UNION
SELECT
*
FROM
Movies
WHERE
movieType="feature";
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
*
FROM
Movies
WHERE
movieType="feature"
UNION ALL
SELECT
*
FROM
Movies
WHERE
movieType="feature";
1
2
3
4
5
+----------------------+------+--------+-----------+------------+-----------+
| title | year | length | movieType | studioName | producerC |
+----------------------+------+--------+-----------+------------+-----------+
| My people,My country | 2019 | 158 | feature | huaxia | 100 |
+----------------------+------+--------+-----------+------------+-----------+
1
2
3
4
5
6
+----------------------+------+--------+-----------+------------+-----------+
| title | year | length | movieType | studioName | producerC |
+----------------------+------+--------+-----------+------------+-----------+
| My people,My country | 2019 | 158 | feature | huaxia | 100 |
| My people,My country | 2019 | 158 | feature | huaxia | 100 |
+----------------------+------+--------+-----------+------------+-----------+

交集 INTERSECT

INTERSECT 交集和 EXCEPTUNION 相似,用于进行其他集合运算。但这种运算不太常用也比较少提及。

1
{主 SELECT 语句} INTERSECT {副 SELECT 语句}

差集 EXCEPT

另外就是 EXCEPT 差集了,这种运算也不太常用。所谓差集就是从集合 A 中去除集合 B 中包含的元素,集合 A 不需要是集合 B 的超集。在一些 SQL 版本中,为 MINUS 关键字。

1
{主 SELECT 语句} EXCEPT {副 SELECT 语句}
补充:除法

在学校数据库的课程中还介绍了一种除法运算,虽然感觉没什么用,但还觉得挺有意思,于是记录一下。

通俗地讲(只会通俗描述),除法 A÷B=CA \div B = C 首先要求除表 BB 的字段是被除表 AA 的子集,于是将 AA 的字段分为 FABF_{A-B}FBF_B 两个部分,则结果表 CC 的字段为 FABF_{A-B}。将 AAFABF_{A-B} 分组,若 FBF_B 的集合是 BB 的超集,则结果表 CC 包含 FABF_{A-B}。集合除法的现实意义在于筛选满足条件的对象,我们将字段集 FABF_{A-B} 当作一个对象,它通常是主键;而 FBF_B 表示该对象的属性,除表 BB 则表示筛选条件集。例如下面的例子:

MovieTickets:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+---- -----+-----------+----------------------+-----------+------------+
| ticketId | userName | movieTitle | movieYear | ticketChan |
+----------+-----------+----------------------+-----------+------------+
| 1 | JamhusTao | My people,My country | 2019 | website |
+----------+-----------+----------------------+-----------+------------+
| 2 | JamhusTao | Star Wars | 1977 | android |
+----------+-----------+----------------------+-----------+------------+
| 3 | Mike | My people,My country | 2019 | ios |
+----------+-----------+----------------------+-----------+------------+
| 4 | Mike | Star Wars | 1977 | website |
+----------+-----------+----------------------+-----------+------------+
| 5 | John | Star Trek: Nemesis | 2002 | website |
+----------+-----------+----------------------+-----------+------------+
| 6 | John | Star Wars | 1977 | android |
+----------+-----------+----------------------+-----------+------------+
| 7 | JamhusTao | Star Trek: Nemesis | 2002 | desktop |
+----------+-----------+----------------------+-----------+------------+

Filter:

1
2
3
4
5
6
7
+----------------------+-----------+------------+
| movieTitle | movieYear | ticketChan |
+----------------------+-----------+------------+
| My people,My country | 2019 | website |
+----------------------+-----------+------------+
| Star Wars | 1977 | android |
+----------------------+-----------+------------+

现在尝试计算 πuserName, movieTitle, movieYear, ticketChan(MovieTickets) ÷ Filter\pi_{\text{userName},~\text{movieTitle},~\text{movieYear},~\text{ticketChan}}(\text{MovieTickets})~\div~\text{Filter}π\pi 是投影(字段选择)的形式化表示)

输出如下:

1
2
3
4
5
+-----------+
| userName |
+-----------+
| JamhusTao |
+-----------+

没错,在这个示例中,除表 Filter\text{Filter} 实际起了筛选器的作用。在这个示例中 FAB={userName}F_{A-B}=\{\text{userName}\}FB={movieType, ticketChan}F_B=\{\text{movieType},~\text{ticketChan}\},该表达式意义就是筛选所有购买了 Filter\text{Filter} 中票的用户,被筛选的用户必须同时满足所有筛选器条件。

在这个示例中,只有 JamhusTao 同时购买过 2019My people,My country 电影来自 website 的票、1977Star Wars 电影来自 android 的票。

除法也是集合运算,但是与上面介绍的三种集合运算都不同的是,除法运算没有其对应的关键字。可以使用下面的方式完成除法运算,还挺复杂的。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
{主表字段 - 副表字段}
FROM
A
GROUP BY
{主表字段 - 副表字段}
HAVING
NOT EXISTS (
SELECT * FROM B
EXCEPT
SELECT {副表字段} FROM A
) AS TM; -- 副表是主表的子集, 即副表与主表的差集为空集