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
SELECTDISTINCT*FROM ({主 SELECT 语句} UNIONALL {副 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" UNIONALL 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 交集和 EXCEPT 与 UNION 相似,用于进行其他集合运算。但这种运算不太常用也比较少提及。
1
{主 SELECT 语句} INTERSECT {副 SELECT 语句}
差集 EXCEPT
另外就是 EXCEPT 差集了,这种运算也不太常用。所谓差集就是从集合 A 中去除集合 B 中包含的元素,集合 A 不需要是集合 B 的超集。在一些 SQL 版本中,为 MINUS 关键字。
1
{主 SELECT 语句} EXCEPT {副 SELECT 语句}
补充:除法
在学校数据库的课程中还介绍了一种除法运算,虽然感觉没什么用,但还觉得挺有意思,于是记录一下。
通俗地讲(只会通俗描述),除法 A÷B=C 首先要求除表 B 的字段是被除表 A 的子集,于是将 A 的字段分为 FA−B 和 FB 两个部分,则结果表 C 的字段为 FA−B。将 A 按 FA−B 分组,若 FB 的集合是 B 的超集,则结果表 C 包含 FA−B。集合除法的现实意义在于筛选满足条件的对象,我们将字段集 FA−B 当作一个对象,它通常是主键;而 FB 表示该对象的属性,除表 B 则表示筛选条件集。例如下面的例子:
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 | +----------------------+-----------+------------+