七、嵌套子查询

Nested Subqueries：

A subquery is aselect-from-where

expression that isnested within another

query.(子查询是嵌套在另一个查询中的select-from-where表达

式)

 

测试一个元组是否是某个集合中的元素，用 in , not in

1.

“Find allthe courses taught in the both the

Fall2009 and Spring 2010 semesters.” 

 Step 1 :

(select courseid 

from section 

where semester = ‟Spring‟ and year= 2010)

 

step 2:

2.

“Find allthe courses taught in the both the

Fall 2009but not in Spring 2010 semesters.”

 

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

 

3.not in , in也可以用在枚举集合上。

select distinct name

from instructor

where name not in (‟Mozart‟,‟Einstein‟);

 

 

Set Comparison：(集合间的比较)

1.

select name

from instructor

where salary >some

(selectsalary

frominstructor

where dept_name=‟Biology‟);

在SQL中some和any是同义的。

对于some,来说只要集合中有一个元素能满足这样的条件就可以。表示存在。

2.

Find thenames of all instructors whose

salary isgreater than the salary of all

instructorsin the Biology department.

select name

from instructor

where salary >all (select salary

               from instructor

               where dept_name= ‟Biology‟);

对于all,来说集合中所有的元素都满足这个关系时才为真。表示任意。

In many cases, using subqueries with

some, any or all is logical equivalent to

equality comparison which uses the

subqueries containing certain aggregation.(

在很多情况下，使用some,any或all的子查询逻辑等价于对使用聚合

的子查询进行相等比较)

 

Test for Empty Relations（测试一个集合是否为空）：

exist ，not exist

例如：

Find all studentswho have taken all

courses offered inthe Biology

department.

 

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

Subqueries in the From Clause（From 子句中的子查询）：

Find the average instructors‟ salaries of

those departmentswhere the average salary

is greater than$42,000. 

 

select dept_name, avg_salary 

from (select dept_name, avg (salary) as avg_salary

from instructor

group bydept_name)

where avg_salary > 42000;

在select-from-where结构中，只要是关系可以出现的地方，都可以用嵌套子查询，因为查询后的结果本质上就是一个关系。

 

With Clause

With子句提供了定义临时视图的方法，该定义只对包含with子句的查询有效。

例如：

1.Find alldepartments with the maximum budget。 

with max_budget (value) as 

         (selectmax(budget)

           fromdepartment)

     selectdept_name,budget

     fromdepartment, max_budget

     wheredepartment.budget = max_budget.value;

2.Find all departments where the total salary is

greater than theaverage of the total salary at all

departments。

with dept_total(dept_name, value) as

        (selectdept_name, sum(salary)

         frominstructor

         groupby dept_name),

dept_total_avg(value) as

       (selectavg(value)

       fromdept_total)

select dept_name

from dept_total, dept_total_avg

where dept_total.value >= dept_total_avg.value;

 

Scalar Subquery(标量子查询)

Scalarsubquery is one which is used where a single value is expected。

找出每个部门指导老师的数量：

selectdept_name, 

             (select count(*) 

                 from instructor 

                where department.dept_name =instructor.dept_name)

             as num_instructors

         from department;

找出那些工资比本部门budget的十分之一多的指导老师的名字。

 select name

        from instructor

        where salary * 10 > 

             (select budget  from department 

                where department.dept_name =instructor.dept_name)

Runtime error if subquery returnsmore than one

result tuple(如果执行后结果不止一个元组，则产生一个运行时错误)