3.8 嵌套子查询 3.8.1 集合成员资格

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

3.8 嵌套子查询

SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。

可以在where子句中嵌套子查询

子查询嵌套在where子句中,通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。
从3.8.1到3.8.4节我们学习在where子句中嵌套子查询的用法。

可以在from子句中嵌套子查询

在3.8.5节我们学习在from子句中嵌套的子查询。

标量子查询

在3.8.7节我们将看到一类被称作标量子查询的子查询,标量子查询可以出现在一个表达式所返回的单个值可以出现的任何地方的。

3.8.1 集合成员资格

SQL允许测试元组在关系中的成员资格。

测试元组是否是集合的成员 in

连接词in测试元组是否是集合中的成员,这里的集合是指是由select子句产生的一组值。

测试元组是否不是集合的成员 not in

连接词not in则测试元组是否不是集合中的成员。

in示例 找出在2009年秋季和2010年春季学期同时开课的所有课程

作为示例,考虑査询”找出在2009年秋季和2010年春季学期同时开课的所有课程”。先前,我们通过对两个集合进行交运算来书写该査询,这两个集合分别是:2009年秋季开课的课程集合与2010年春季开课的课程集合。
现在我们采用另一种方式,查找在2009年秋季开课的所有课程,看它们是否也是2010年春季开课的课程集合中的成员。
很明显,这种方式得到的结果与前面相同,但我们可以用SQL中的in连接词书写该查询。

先写出子查询

我们从找出2010年春季开课的所有课程开始,写出子查询:

1
2
3
4
5
(
select course_id
from section
where semester='Spring' and year=2010
)

完整查询

然后我们需要从子查询形成的课程集合中找出那些在2009年秋季开课的课程。为完成此项任务可将子查询嵌入外部查询的where子句中。最后的查询语句是:

1
2
3
4
5
6
7
select distinct course_id
from section
where semester='Fall' and year= 2009 and course_id in (
    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 distinct course_id
from section
where semester='Fall' and year= 2009 and course_id in (
    select course_id
        from section
        where semester='Spring' and year = 2010
);
+-----------+
| course_id |
+-----------+
| CS-101    |
+-----------+
1 row in set

该例说明了在SQL中可以用多种方法书写同一查询。这种灵活性是有好处的,因为它允许用户用最接近自然的方法去思考查询。

not in示例 找出所有在2009年秋季学期开课,但不在2010年春季学期开课的课程

1
2
3
4
5
6
7
select distinct 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
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select distinct 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

in和not in用于枚举集合

in和not in操作符也能用于枚举集合。下面的查询找出既不叫"Mozart",也不叫"Einstein"的教师的姓名

1
2
3
select distinct name
from instructor
where name not in ('Mozart','Einstein');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select distinct name
from instructor
where name not in ('Mozart','Einstein');
+------------+
| name       |
+------------+
| Srinivasan |
| Wu         |
| El Said    |
| Gold       |
| Katz       |
| Califieri  |
| Singh      |
| Crick      |
| Brandt     |
| Kim        |
+------------+
10 rows in set

测试多个成员资格

在前面的例子中,我们是在单属性关系中测试成员资格。在SQL中测试任意关系的成员资格也是可以的。
例如,我们可以这样来表达查询”找出(不同的)学生总数,他们选修了ID为10101的教师所讲授的课程段“

1
2
3
4
5
6
7
select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in (
    select course_id, sec_id, semester,year
        from teaches
        where teaches.ID=10101
);
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in (
    select course_id, sec_id, semester,year
        from teaches
        where teaches.ID=10101
);
+--------------------+
| count(distinct ID) |
+--------------------+
|                  6 |
+--------------------+
1 row in set