3.8嵌套子查询

1.       子查询是嵌套在另一个查询中的select-from-where表达式。

3.8.1用于判断元素是否属于集合

2.       子查询可以出现在where子句中，与in 或者not in连用以判断某个属性值是否是一个集合的元素。

3.       可用下面的语句来查找09年秋天和10年春天都开设了的课程 :

4.       selectdistinct course id
from section
where semester= ’Fall’and year=2009 and
course id in(selectcourse id
from section
where semester= ’Spring’and year=2010);

5.       虽然也可以用intersect来实现这个查询，但上面的写法无疑比较有新意。

6.       下面这种形式是允许的

7.       where (a,b,c) in (select a,b,c from t);

3.8.2与集合元素进行比较

8.       找出生物系不领最低工资的老师的查询也可以写成：

9.       select name
from instructor
where salary> some(selectsalary
from instructor
where dept name= ’Biology’);

10.   SQL还支持，<some,<=some,>=some,= some, and<> some，其中=some与in等价，而<>some则与not in 等价

11.   与some对应，还有< all,<=all,>=all,= all, and<> all

12.   <>all 与not in等价，但是=all 与in不等价

13.   下面的查询可用于查找平均工资最高的学院

14.   select dept name
from instructor
group by dept name
having avg (salary)>=all(select avg(salary)
   from instructor
   group by dept name);

3.8.4检查关系是否为空

15.   exists表达式求值：如果传给它的子查询的结果不为空，返回true否则返回false

16.   外层查询里的表名可以用在嵌套查询里。我们称使用了外层查询中定义的关系别名的子查询为关联子查询correlated subquery

17.   在包含子查询的查询中要考虑关系别名的作用域的问题。在子查询中只能使用在这个查询以及包含这个子查询的查询中定义的关系别名。如果一个关系别名在子查询和外层查询中都定义了，使用子查询中定义的别名。

18.   查找上了生物学院开的所有课程的学生的ID和名字：

19.   selectdistinct S.ID,S.name
from studentas S
where not exists((selectcourse id
from course
where dept name= ’Biology’)
except
(selectT.course id
from takesas T
where S.ID= T.ID))

3.8.4判断集合中是否有重复值

20.   unique 只有一部分数据库支持。如果传给它的子查询的结果为空或者其中没有重复值，返回true否则返回false

21.   当传入的子查询的结果中包含null值，则即使其它值都相等，但这两个元组也不会被视为相等。

3.8.5From子句中的子查询

22.   因为select-from-where表达式返回的是一个关系，因此在一个查询中凡是要用到关系的地方都可以用select-from-where表达式。

23.   多数数据库支持对from子句中的子查询重命名——Oracle是个例外：

24.   select dept name,avg salary
from (selectdept name,avg (salary)
from instructor
group by dept name)
as dept avg(dept name,avg salary)
where avg salary> 42000;

25.   from子句中的子查询不能使用在这个from子句中定义的其他的关系别名

26.   但是sql:2003支持Lateral子句，包含在这个子句中的子查询中可使用子查询所属的from子句中已经出现过的关系中的属性

27.   示例：查找每个老师的名字、工资、及该老师所在学院的平均工资：

28.   select name,salary,avg salary
from instructor I1,lateral(select avg(salary) as avg salary
                       from instructor I2
                       where I2.dept name=I1.dept name);

29.   支持lateral关键字的数据库不多，书里提到的只有DB2

3.8.6 With子句

30.   With子句用于定义只在包含With子句的语句中可见的临时关系。

31.   with max budget(value)as
(select max(budget)
from department)
select budget
from department,max budget
where department.budget= max budget.value;

32.   大多数数据库都支持With子句，少数不支持。

33.   使用with子句相较使用子查询逻辑更清楚，可读性更好，同时它还允许在一个查询中多次使用某个视图定义（With子句构造的临时关系可被视为一个视图）

34.   查找工资总和大于各个 学院 的工资总和 的平均值 的学院 :

35.   with dept total(dept name,value)as
(selectdept name,sum(salary)
from instructor
group by dept name),
dept total avg(value)as
(select avg(value)
from dept total)
select dept name
from dept total,dept total avg
where dept total.value>= dept total avg.value;

3.8.7 Scalar 标量子查询

36.   Scalar subquery :其结果只含一个元组，且该元组只有一个属性。

37.   能使用只返回单个值的表达式的地方就能使用标量子查询

38.   标量子查询可出现在Select子句中:比如当我们要列出各个学院的名称及这个学院的老师人数时：

39.   select dept name,
    (select count(*)
    frominstructor
    wheredepartment.dept name=instructor.dept name)
    asnum instructors
from department;

40.    上面的查询中用绿色标记出来的部分使用的是外部查询中的关系名。像这样在子查询中使用外部查询中的关系名的情况有点类似于编程写嵌套循环的时候，里面的循环可以使用外层循环中的变量。

41.   标量子查询的结果仍然是一个关系，但是当标量子查询用在需要单个值的地方时，SQL会从子查询的结果关系中提取并返回那个值。