13.2.14 集合运算

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

13.2.14 集合运算

select语句查询的结果是一个包含多条数据的结果集,类似于数学里的集合,可以进行交( Intersect)、并( union)和差( minus)运算, select查询得到的结果集也可能需要进行这三种运算.
为了对两个结果集进行集合运算,这两个结果集必须满足如下条件。

  • 两个结果集所包含的数据列的数量必须相等。
  • 两个结果集所包含的数据列的数据类型也必须一一对应。

1. union运算

unIon运算的语法格式如下:
select语句 union select语句
下面的SQL语句查询出所有教师的信息和主键小于4的学生信息。

1
2
3
select * from teacher_table
union
select student_id,student_name from student_table;

运行效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from teacher_table
union
select student_id,student_name from student_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
|          1 | 张三         |
|          2 | 张三         |
|          3 | 李四         |
|          4 | 王五         |
|          5 | _王五        |
|          6 | NULL         |
|          7 | 赵六         |
+------------+--------------+
10 rows in set

2. minus运算

minus运算的语法格式如下:
select语句 minus select语句
上面的语法格式十分简单,不过很遗憾,MySQL并不支持使用minus运算符,因此只能借助于子查询来实现上面的minus运算。
假如想从所有学生记录中”减去”与老师记录的ID相同、姓名相同的记录,则可进行如下的minus运算:

1
2
3
select student_id,student_name from student_table
minus
select teacher_id,teacher_name from teacher_table;

不过, MySQL并不支持这种运算。但可以通过如下子查询来实现上面运算

1
2
3
4
5
6
select student_id,student_name from student_table
where (student_id,student_name)
not in
(
    select teacher_id,teacher_name from teacher_table
);

运行效果:

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
44
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select student_id,student_name from student_table
where (student_id,student_name)
not in
(
    select teacher_id,teacher_name from teacher_table
);
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|          1 | 张三         |
|          2 | 张三         |
|          3 | 李四         |
|          4 | 王五         |
|          5 | _王五        |
|          6 | NULL         |
|          7 | 赵六         |
+------------+--------------+
7 rows in set

mysql>

3. intersect运算

intersect运算的语法格式如下:
select语句 intersect select语句
上面的语法格式十分简单,不过很遗憾, MySQL并不支持使用intersect运算符,因此只能借助于多表连接查询来实现上面的intersect运算。
假如想找出学生记录中与老师记录中的ID相同、姓名相同的记录,则可进行如下的intersect运算:

1
2
3
select student_id,student_name from student_table
intersect
select teacher_id,teacher_name from teacher_table;

不过, MySQL并不支持这种运算。但可以通过如下多表连接查询来实现上面运算。

1
2
3
select student_id,student_name from student_table
join teacher_table
on (student_id=teacher_id and student_name=teacher_name);

需要指出的是,如果进行intersect运算的两个select子句中都包括了where条件,那么将intersect运算改写成多表连接査询后还需要将两个where条件进行and运算。假如有如下Intersect运算的SQL语句:

1
2
3
select student_id,student_name from student_table where student_id<4
intersect
select teacher_id,teacher_name from teacher_table where teacher_name like '李%';

上面语句改写如下:

1
2
3
4
5
select student_id,student_name from student_table
join
teacher_table
on(student_id=teacher_id and student_name=teacher_name)
where student_id<4 and teacher_name like '李%';