Sql学习笔记4——嵌套子查询（上）

嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中时，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

1、集合成员资格

SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的。连接词not in测试元组是否不是集合中的成员。

考虑“找出在2009年秋季和2010年春季学期同时开课的所有课程。”按之前所学知识，可以通过对两个集合进行并运算来书写该查询。如下：

　　（select course_id

　　from section

　　where semester=‘Fall’ and year=2009）

　　intersect

　（select course_id

　　from section

　　where semester=‘Spring’ and year=2010）;

现在考虑，先找出在2009年秋季开课的所有课程，再看它们是否也是2010年春季开课的课程集合中的成员。很明显，这个查询与前者结果一样。

　　（select distinct course_id　　/*此查询不会自动去重复，因此必须要用distinct*/

　　from section

　　where semester=‘Fall’ and year=2009）and

　　　  course_id in（select course_id

　　　　　　　　　　from section

　　　　　　　　　　where semester=‘Spring’ and year=2010）;

in 与not in也可以用于枚举集合。如，下列查询找出既不叫A也不叫B的教师的姓名：

　　select distinct name

　　from instructor

　　where name not in（'A','B');

2、集合的比较

考虑查询“找出满足下面条件的所有老师的姓名，他们的工资至少比Biology系某一个教师的工资要高。”在之前，我们写法如下：

　　select distinct T.name

　　from instructor as T,instructor as S　　/*更名运算常用场合*/

　　where T.salary>S.salary and S.dept_name='biology';

在SQL中，短语“至少比某一个要大”在SQL中用>some表示，此结构允许我们用一种更加贴近字面表述的查询：

　　select name

　　from instructor

　　where salary>some(select salary

　　　　　　　　　　　　　from instructor

　　　　　　　　　　　　　where dept_name='Biology');

此外，SQL也允许<some, <=some, >=some,=some和<>some的比较。易知=some等价于in，然而<>some并不等价于not in。

稍微改一下上述查询的条件，考虑“找出满足下面条件的所有老师的姓名，他们的工资至少比Biology系每个教师的工资要高。”

结构>all对应词组”比所有的都大“。写法如下：

　　select name

　　from instructor

　　where salary> all(select salary

　　　　　　　　　　　　　from instructor

　　　　　　　　　　　　　where dept_name='Biology');

类似地，SQL也允许<all,<=all,>=all,=all和<>all的比较。易知<>all 等价于not in 但=all不等价于in

3、空关系测试：exists与 not exists（*难点）

SQL还有一个特性是可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空时返回true。

 使用exists结构，写出查询“找出2009年秋季学期和2010年春季学期同时开课的所有课程。”

　　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);

上述查询说明了SQL的一个特性，来自外层的查询的一个相关名称（上述查询中的S)可以用在where子句的子查询中。这种使用了来自外层相关名称的子查询称为相关子查询。

注：只要有exists都是相关子查询，exists返回的只是一个bool值，测试子查询结果中是否有元组。

not exists 结构用于测试子查询结果中是否不存在元组。可以用not exists来模拟集合包含操作：

例如，关系A包含关系B可以写成”not exists（B except A）“

考虑查询”找出选修了biology系开设所有课程的学生“。我们可以理解为：某些学生选修的课程包含了biology系所开设的所有课程。使用not exists-except结构如下：

　　select S.id,S.name

　　from student as S

　　where not exists((select course_id

　　　　　　　　　　　from course

　　　　　　　　　　  where dept_name=‘biology’）　　/*子查询1，找出biology系开设的所有课程的集合*/

　　　　　　　　　　 except

　　　　　　　　　　(select S.id

　　　　　　　　　　from takes as T

　　　　　　　　　　where S.id=T.id));    /*子查询2，找出S.id选修的所有课程*/

本例中，外层select对每个学生测试其所选修的所有课程集合是否包含biology系所开设的所有课程集合。

 4）重复元组存在性测试

SQL提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique结构将返回true。（此结构并未被广泛实现，sql server2012都不支持）

考虑查询”找出所有在2009年最多开设一次的课程“，如下：

　　select T.course_id

　　from course as T

　　where unique（select R.course.id

　　　　　　　　　　from section as R

　　　　　　　　　　where T.course_id=R.course_id and R.year = 2009);

若不使用unique，如下：

　　select T.course_id

　　from course as T

　　where 1>=（select count(R.course.id)

　　　　　　　　　　from section as R

　　　　　　　　　　where T.course_id=R.course_id and R.year = 2009);

我们可以用not unique结构测试在一个子查询中是否存在重复元组。

考虑查询“找出所有在2009年最少开设两次的课程”

　　select T.course_id

　　from course as T

　　where not unique（select R.course.id

　　　　　　　　　　from section as R

　　　　　　　　　　where T.course_id=R.course_id and R.year = 2009);

　　

 

转载于:https://www.cnblogs.com/Dezhong-chen/p/4439296.html