SQL——嵌套子查询

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

1、集合成员资格

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

示例：

#找出在2018年春季和秋季同时开课的课程 
#前面有提到in 可以替代集合的交运算
#not in 就不举例了，相信读者也能够写出（not in 相当于集合的差运算）
SELECT DISTINCT S.course_id
FROM section AS S
WHERE S.semester='spring'AND S.year=2018 AND
course_id IN (SELECT course_id
                FROM section as T
                WHERE  T.semester = 'fall' and T.year=2018
);

同时，in和not in也能用于枚举型集合。例如：

-- 找出名字不叫Smith 和wu的所有教师的信息
SELECT DISTINCT *
FROM instructor
WHERE name NOT IN ('smith','wu');

instructor表 结果表

2、集合的比较

-- 找出比其中一个历史系老师工资高的教师信息
SELECT DISTINCT T.*
FROM instructor AS T,instructor AS S
WHERE T.salary>S.salary AND S.dept_name = 'history';

对于上面的查询,可以用另一种方式书写，短语“至少比某一个大”在SQL中用>some表示，于是可以用下面这种更加贴切的文字表达的形式书写：

SELECT *
FROM instructor
WHERE salary>SOME ( SELECT salary 
                    FROM instructor   
                    WHERE dept_name = 'history');

两种书写形式的结果表 instructor原表

同样的，也有<some,=some,<=some,>=some,和<>some的比较,其中=some等价于in，<>some不等价于not in；

当我们要找出比所有历史系老师工资高的教师信息时，可以通过>all来实现，例如：

SELECT *
FROM instructor
WHERE salary>ALL (SELECT salary FROM instructor WHERE dept_name = 'history');

>all结果表

同some，也有<all,<=all,>=all,=all,<>all的比较，<>all 等价于not in，但=all并不等价于in。

3、空关系测试

SQL还有一个特性是可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空时返回true值。使用exists，我们还能够使用另一种方式书写实现上文实现“找出在2018年春季和秋季同时开课的课程 ”的查询。

SELECT DISTINCT course_id
FROM section AS  S
WHERE S.semester = 'spring' AND S.year = 2018 AND
  exists(SELECT *
         FROM section AS T
         WHERE T.semester = 'fall' AND T.year=2018 AND S.course_id=T.course_id);

上述的示例中还反应了SQL的另一特性，来自外层查询的一个相关名称（上述查询中的S）可以用在where子句的子查询中。使用外层查询相关名称的子查询称作相关子查询。

在包含了子查询的操作中，在相关名称上可以应用作用域规则。根据此规则，在一个子查询中只能使用该子查询本身定义的，或者包含该子查询的任何查询中定义的相关名称。emmm……类似于全局变量于局部变量的作用域关系。

同理，可以用not exists结构来测试子查询结果集中是否不存在元组。

同时，我们还可以使用not exists结构来模拟集合的包含操作：将“关系A包含于关系B”写成not exists（B except A），例如：

#找出选修了biology系开设的所有课程的学生
#takes关系表是表示某一个学生所选修的课程集合

SELECT S.id,S.name
FROM student AS S 
WHERE NOT exists(
    (
      #找出biology系开设的所有课程集合
      SELECT course_id
      FROM course
      WHERE dept_name = 'biology'
    )EXCEPT (
      #找出S.id选修的所有课程集合
      SELECT T.course_id
      FROM takes AS T 
      WHERE S.id = T.id
    )
);
#外层select对每个学生测试其选修的所有课程集合是否包含biology系开设的所有课程。

使用mysql的人（因为mysql没有except运算，可以参照之前SQL——集合运算），可以用下面句子实现上述效果：

select distinct S.id , S.name
from student as S ,takes as T
where S.id = T.id and course_id in (
#找出biology系开设的course_id集合
select course_id
from course
where dept_name = 'biology');

也可以使用连接的方式替换上面的方式：

SELECT S.id,S.name
FROM student AS S
WHERE S.id = SOME (
  SELECT id
  FROM
  (
#找出biology系的course_id关系，再将这个关系通过course_id与takes连接
    SELECT course_id
    FROM course
    WHERE dept_name = 'biology'
  )AS C
  JOIN takes AS T USING (course_id)
);

结果表 takes表 student表 course表

注：数据库的数据纯属虚构，测试使用；

其实，之前有讲到如何替换except，如果按照之前的替换方法，应该是以下的书写形式的：
但是，这样书写是有错误的。为什么呢？因为在子查询中join子句是无法参照（或者说引用更合适）外部表的，也就说这里会报这样的错误：' Unknown column 'S.id' in 'where clause'。

这也提醒我们，实现某个查询功能，首先搞清楚它的实现逻辑，这些逻辑逻辑可能有好几种实现方法。

SELECT S.id,S.name
FROM student AS S
WHERE NOT exists(
  SELECT course_id
  FROM
  (
    SELECT course_id
    FROM course
    WHERE dept_name = 'biology'
  )AS C
  LEFT JOIN
  (
    SELECT course_id
    FROM takes AS T
    WHERE S.id = T.id
  )AS N USING (course_id)
  WHERE N.course_id IS NULL
);

4、重复元组存在性测试

SQL提供一个布尔函数unique，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值。

-- 找出所有在2018年最多开设一次的课程
SELECT T.*
FROM course AS T
WHERE UNIQUE(
    SELECT R.course_id
    FROM section AS R
    WHERE T.course_id=R.course_id AND R.year = 2018
);

不过令人蛋疼的是，mysql没法识别出来，不过没关系，我们可以用下列方法来等价它：

#不重复；如果要测试重复，则将等于替换成<
SELECT T.*
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 = 2018
);

5、from子句中的子查询

SQL允许在from子句中使用子查询表达式。再次采用的主要观点是：任何select-from-where表达式返回的结果都是关系，因而可以插入到另一个select-from-where中任何关系可以出现的位置。对于下面这个查询句子，是找出系平均工资超过15000的那些戏中的教师的平均工资

SELECT dept_name,avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name
HAVING avg(salary)>15000;

如果在from插入子查询实现，则如下：

SELECT dept_name,avg_salary
FROM (
  SELECT dept_name,avg(salary) AS avg_salary
  FROM instructor
  GROUP BY dept_name
)AS S 
WHERE S.avg_salary>15000;

注：很多SQL实现都支持在from子句中嵌套子查询，还有就是SQL实现要求对一个子查询结果关系都给一个名字，即使该名字从不被引用，例如mysql就是这样（因为笔者就是用的MySQL[捂脸]）。当我们使用了from子句的子查询后，having就显得不必要，因为having子句使用的谓词出现在外层查询的where子句中，当然，不是说不可以用。

对于下面的例子：“找出在所有系中工资总额最大的系，以及总额是多少”，不使用from子句的子查询，having子句是无能为力，但用from子句中的子查询却能轻易实现。

#按照系名分组，计算每个分组的工资总额，从中挑取最大值。
SELECT max(tol_salary)
FROM (
  SELECT dept_name,sum(salary) AS tol_salary
  FROM instructor
  GROUP BY dept_name
)AS dept_tol;

6、with子句

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

#我感觉有点怀疑人生，因为mysql竟然不支持with子句，不过还是写一下SQL语句的吧
with max_budget(value) as
    (select max(budget)
      from department)
select budget 
from department,max_budget
where department.budget = max_budget.budget;

with子句最主要的作用是是的逻辑更清晰，我们也是可以用from子句或者where子句中的嵌套子查询实现with子句的，只不过看起来很繁琐，难懂。
利用嵌套子查询实现上面句子是这样的：

SELECT budget
FROM department,(
  SELECT max(budget) as val
  FROM department
  )AS max_budget
WHERE department.budget = max_budget.val;

6、标量子查询

SQL允许子查询出现在单个值得表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组，这就叫做标量子查询:
示例：‘列出所有的系以及它们拥有的教师’

#该嵌套子查询的结果只有一行一列，也就是只返回包含单个属性的单个元组。
SELECT dept_name,(
    SELECT count(*)
    FROM instructor
    WHERE department.dept_name = instructor.dept_name
)AS num_instr
FROM department;

附件：
主码用下划线标注，外码依赖用从参照关系的外码属性到被参照的主码属性之间的箭头表示

数据库的模式图