SQL语言（二）数据库查询语言第二部分（嵌套子查询）

嵌套子查询机制

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

• 集合成员资格
• 集合的比较
• 空关系测试
• 重复元组存在性测试
• from子句中的子查询
• with子句

一、集合成员资格

SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的，对应的还有not in。
例1，找出在2009年秋季和2010年春季学期同时开课的所有课程。

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

二、集合的比较

关键词 some all。
例，找出平均工资最高的系：

select dept_name
from instructor
group by dept_name
having avg (salary) >= all (select avg (salary)
							 from instructor
							 group by dept_name);

三、空关系测试

SQL还有一个特性可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询为空时返回true值。
例，找出在2009年秋季学期和2010年春季学期通识开课的所有课程，使用exists结构，重写该查询：

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

我们还可以使用not exists结构模拟集合包含（即超集）操作：可将“关系A包含关系B”写成“not exists(B except A)”
例，找出选修了Biology系开设的所有课程的学生，使用except结构，写该查询：

select distinct S.ID, S.name
from student as S
where not exists ((  select course_id		//Biology系开设的所有课程
					 from course
					 where dept_name = ‘Biology’)
					 except
					 (select T.course_id		//学生选秀的所有课程
					 from takes as T
					 where S.ID = T.ID));

四、重复元组存在性测试

SQL提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组unique结构将返回true值。
例1，找出所有在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换成1>=。

例2，找出所有在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);

五、from子句中的子查询

SQL允许在from子句中使用子查询表达式。任何select-from-where表达式返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置（比如from后面）。

例，找出系平均工资超过42 000美元的那些系中教师的平均工资。

select dept_name, avg_salary
from (select dept_name, avg (salary)	
		 from instructor
		 group by dept_name)
		 as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

例，找出在所有系中工资总额最大的系。

select max(tot_salary)
from (select dept_name, sum(salary)
	from instructor
	group by dept_name) as dept_total(dept_name, tot_salary);

六、with子句

with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效。
例，找出具有最大预算值的系。

with max_budget (value) as		//定义临时关系
	 (select max(budget)	
	 from department)
select budget
from department, max_budget		//使用临时关系
where department.budget = max_budget.value;  //使用临时关系