3.5 集合运算

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 6.5k 阅读时长 ≈ 6 分钟

3.5 集合运算

SQL作用在关系上的union、 intersect和except运算对应于数学集合论中的并(∪)、交(∩)和差(-)运算。我们现在来构造包含在两个集合上使用union、 intersect和except运算的查询。

SQL查询 在2009年秋季学期开设的所有课程的集合

1
2
3
select course_id
from section
where semester ='Fall' and year=2009;
1
2
3
4
5
6
7
8
9
10
11
mysql> select course_id
from section
where semester ='Fall' and year=2009;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
+-----------+
3 rows in set

SQL查询 在2010年春季学期开设的所有课程的集合

1
2
3
select course_id
from section
where semester='Spring' and year=2010;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select course_id
from section
where semester='Spring' and year=2010;
+-----------+
| course_id |
+-----------+
| CS-101    |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-319    |
| CS-315    |
+-----------+

3.5.1 并运算 union

为了找出在200年秋季开课,或者在2010年春季开课或两个学期都开课的所有课程,我们可写查询语句:

1
2
3
4
5
6
7
8
9
(
    select course_id
    from section
    where semester ='Fall' and year=2009
)union(
    select course_id
    from section
    where semester='Spring' and year=2010
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> (
    select course_id
    from section
    where semester ='Fall' and year=2009
)union(
    select course_id
    from section
    where semester='Spring' and year=2010
);
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-315    |
+-----------+
8 rows in set

有些SQL实现不支持union运算

尽管这是SQL-92标准的一部分,但某些SQL实现中可能不支持这种语法,经过我的测试MySQL是支持union运算的。

为便于阅读可将两条合并的查询放在括号中

上述SQL语句中我们在每条select-from- where语句上使用的括号是为了方便阅读,括号是可省略的,也就是说写成如下形式也是可以的:

1
2
3
4
5
6
7
select course_id
from section
where semester ='Fall' and year=2009
union
select course_id
from section
where semester='Spring' and year=2010;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select course_id
    from section
    where semester ='Fall' and year=2009
union
select course_id
    from section
    where semester='Spring' and year=2010;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-315    |
+-----------+
8 rows in set

union运算会自动去除重复

与select子句不同, union运算自动去除重复。

union all运算会保留重复

如果我们想保留所有重复,就必须用union all代替unIon:

1
2
3
4
5
6
7
select course_id
from section
where semester ='Fall' and year=2009
union all
select course_id
from section
where semester='Spring' and year=2010;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select course_id
    from section
    where semester ='Fall' and year=2009
union all
select course_id
    from section
    where semester='Spring' and year=2010;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
| CS-101    |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-319    |
| CS-315    |
+-----------+
10 rows in set

3.5.2 交运算 intersect

SQL查询 找出在2009年秋季和2010年春季同时开课的所有课程的集合

1
2
3
4
5
6
7
select course_id
from section
where semester ='Fall' and year=2009
intersect
select course_id
from section
where semester='Spring' and year=2010;

保留重复intersect all

intersect运算自动去除重复。如果我们想保留所有重复,就必须用intersect all代替intersect:

MySQL不支持intersect运算

经过我的测试MySQL好像不支持intersect运算符,所以MySQL中得通过其他方式实现交运算。

MySQL中通过union运算实现交运算

原理就是求table1与table2不去除重复条目的并集，然后按想要的属性分组，取其中重复条目

1
2
3
4
5
6
7
8
9
10
select * from (
    select course_id
        from section
        where semester ='Fall' and year=2009
    union all
    select course_id
        from section
        where semester='Spring' and year=2010
) S 
group by course_id having count(*)>1;

由于这种方式还有一个条件是table1和table2中各自不能有重复值,不然结果不正确,所以查询的时候需要使用select distinct先取出重复值.

SQL查询 找出在2009年秋季所有课程的集合

1
2
3
select distinct course_id
from section
where semester ='Fall' and year=2009;

SQL查询 找出在和2010年春季开课的所有课程的集合

1
2
3
select distinct course_id
from section
where semester='Spring' and year=2010;

SQL查询 找出在2009年秋季和2010年春季同时开课的所有课程的集合

1
2
3
4
5
6
7
8
9
10
11
select * from 
(
select distinct course_id
    from section
    where semester ='Fall' and year=2009
union all
    select distinct course_id
    from section
    where semester='Spring' and year=2010
) S 
group by course_id having count(*)>1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> select distinct course_id
from section
where semester ='Fall' and year=2009;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
+-----------+
3 rows in set

mysql> select distinct course_id
from section
where semester='Spring' and year=2010;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-315    |
| CS-319    |
| FIN-201   |
| HIS-351   |
| MU-199    |
+-----------+
6 rows in set

mysql> select * from (
    select distinct course_id
        from section
        where semester ='Fall' and year=2009
    union all
    select distinct course_id
        from section
        where semester='Spring' and year=2010
) S 
group by course_id having count(*)>1; 
+-----------+
| course_id |
+-----------+
| CS-101    |
+-----------+
1 row in set

3.5.3 差运算 except

SQL查询 找出在2009年秋季学期开课但不在2010年春季学期开课的所有课程

1
2
3
4
5
6
7
select course_id
from section
where semester ='Fall' and year=2009
except
select course_id
from section
where semester='Spring' and year=2010;

except运算从其第一个输入中输出所有不出现在第二个输人中的元组

保留重复except all

except运算在执行集差操作之前自动去除输入中的重复,如果我们想保留所有重复,就必须用except all代替except:

Oracle使用minus代替except

某些SQL实现,特别是Oracle,使用关键词minus代替except。

MySQL不支持except运算符

MySQL中通过not in实现差运算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> select course_id
from section
where semester ='Fall' and year=2009;
+-----------+
| course_id |
+-----------+
| CS-101    |
| CS-347    |
| PHY-101   |
+-----------+
3 rows in set

mysql> select course_id
from section
where semester='Spring' and year=2010;
+-----------+
| course_id |
+-----------+
| CS-101    |
| FIN-201   |
| MU-199    |
| HIS-351   |
| CS-319    |
| CS-319    |
| CS-315    |
+-----------+
7 rows in set

mysql> select course_id
from section
where semester ='Fall' and year=2009 and course_id not in(
    select course_id
        from section
        where semester='Spring' and year=2010
);
+-----------+
| course_id |
+-----------+
| CS-347    |
| PHY-101   |
+-----------+
2 rows in set

参考链接

https://blog.csdn.net/qq_39023116/article/details/79008085
https://www.w3cschool.cn/mysql/mysql-vge12oye.html