3.8.3 空关系测试

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

3.8.3 空关系测试

SQL还有一个特性可测试一个子查询的结果中是否存在元组。

exists

exists结构在作为参数的子查询非空时返回true值。使用exists结构,我们还能用另外一种方法书写査询”找出在2009年秋季学期和2010年春季学期同时开课的所有课程“

1
2
3
4
5
6
7
select course_id
from section as S
where semester='Fall' and year=2009 and exists(
    select *
    from section as T
    where semester ='Spring' and year=2010 and S.course_id= T.course_id
);
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select course_id
from section as S
where semester='Fall' and year=2009 and exists(
    select *
    from section as T
    where semester ='Spring' and year=2010 and S.course_id= T.course_id
);
+-----------+
| course_id |
+-----------+
| CS-101    |
+-----------+
1 row in set

上述査询还说明了SQL的一个特性,来自外层查询的一个相关名称(上述查询中的S)可以用在where子句的子查询中。

相关子查询

使用了来自外层查询相关名称的子查询被称作相关子查询(correlated subquery)。

子查询只能使用自己定义的相关名称,或者外层查询定义的相关名称

在包含了子查询的查询中,在相关名称上可以应用作用域规则。根据此规则,在一个子查询中只能使用此子查询本身定义的,或者在包含这个子查询的任何外部查询中定义的相关名称。

内外层相关名称重名时,内层相关名称有效

如果一个相关名称既在子查询中定义,又在包含该子查询的查询中定义,则子查询中的定义有效。这条规则类似于编程语言中通用的变量作用域规则(重名局部变量覆盖成员变量.)。

not exists

我们可以用not exists结构测试子查询结果集中是否不存在元组。

关系A是否包含关系B not exists( B except A)

我们可以使用not exists结构模拟集合包含(即超集)操作:我们可将”关系A包含关系B“写成” not exists( B except A)“。(尽管contains运算符并不是当前SQL标准的一部分,但这一运算符曾出现在某些早期的关系系统中。)

SQL查询 找出选修了Biology系开设的所有课程的学生

为了说明not exists操作符,考虑查询”找出选修了Biology系开设的所有课程的学生“。使用except结构我们可以书写此查询如下:

1
2
3
4
5
6
7
8
9
10
11
select S.ID,S.name
from student as S
where not exists((
    select course_id
    from course
    where dept_name ='Biology'
) except (
    select T.course_ID
    from takes as T
    where S.ID= T.ID
));

找出Biology系开设的所有课程集合

1
2
3
select course_id
from course
where dept_name ='Biology'
1
2
3
4
5
6
7
8
9
10
11
mysql> select course_id
from course
where dept_name ='Biology';
+-----------+
| course_id |
+-----------+
| BIO-101   |
| BIO-301   |
| BIO-399   |
+-----------+
3 rows in set

找出S.ID选修的所有课程

1
2
3
select T.course_ID
    from takes as T
    where S.ID= T.ID
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
mysql> select S.ID ,T.course_ID
from takes as T,student as S
where S.ID= T.ID;
+-------+-----------+
| ID    | course_ID |
+-------+-----------+
| 98988 | BIO-101   |
| 98988 | BIO-301   |
| 00128 | CS-101    |
| 00128 | CS-347    |
| 12345 | CS-101    |
| 12345 | CS-190    |
| 12345 | CS-315    |
| 12345 | CS-347    |
| 54321 | CS-101    |
| 54321 | CS-190    |
| 76543 | CS-101    |
| 76543 | CS-319    |
| 76653 | EE-181    |
| 98765 | CS-101    |
| 98765 | CS-315    |
| 23121 | FIN-201   |
| 19991 | HIS-351   |
| 55739 | MU-199    |
| 44553 | PHY-101   |
| 45678 | CS-101    |
| 45678 | CS-101    |
| 45678 | CS-319    |
+-------+-----------+
22 rows in set

这样,外层select对每个学生测试其选修的所有课程集合是否包含Biology系开设的所有课程集合。