SQL Server--嵌套查询

嵌套查询

1.带有IN谓词的子查询。

  在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询（nested query）。

举一个例子：

 select Sname
 from Student
 where Sno in
		(select Sno
		from SC
		where Cno='2'
		);

  上层的查询称为外层查询或父查询，下层查询块称为内层查询或子查询。

  SQL语言允许多层嵌套查询，即一个子查询还可以嵌套其他子查询。但是子查询的select语句中不能使用 order by 子句，order by 子句只能对最终的查询结果排序。

  嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强 SQL 的查询能力。以层层嵌套的方式来构造程序正是SQL“结构化”的含义所在。
 

1.带有IN谓词的子查询。

  在嵌套查询中，子查询的结果往往是一个集合。

【例 3.55】 査询与“刘晨”在同一个系学习的学生。

（1）先分步进行，最后在嵌套查询，验证结果。

  ①先找出“刘晨”的所在系。

  select Sdep
  from Student
  where Sname='刘晨';

结果如图：

  ②然后查找所有在CS系学习的学生。

  select Sno,Sname,Sdep
  from Student
  where Sdep='CS';

（2）也可以直接嵌套查询。

select Sno,Sname,Sdep
from Student
where Sdep in
		(select Sdep
		from Student
		where Sname='刘晨'
		);

  本例中，子查询的查询条件不依赖父查询，称为不相关查询。

（3）本例中也可以使用自身连接完成。

select S1.Sno,S2.Sname,S3.Sdep
from Student S1,Student S2
where S1.Sdep=S2.Sdep and S2.Sname='刘晨';

  可以发现运行完结果都是一样的。

  实现同一个查询请求可以有多种方法，不同的方法其执行效率可能也会有差别。

【例 3.56】 查询选修了课程名为“信息系统”的学生学号和姓名。

  本题要涉及三个表，具体代码如下：

  select Sno,Sname
  from Student
  where Sno in
		(select Sno
		from SC
		where Cno in
				(select Cno
				from Course
				where Cname='信息系统'
				)
		);

  本题也可以使用连接查询实现。

  select Student.Sno,Sname
  from Student,SC,Course
  where Student.Sno=SC.Sno and
	  SC.Cno=Course.Cno and
	  Course.Cname='信息系统';

  有些嵌套查询可以用连接运算替代，有些是不能替代的。在涉及多个关系时，用嵌套查询逐步求解层次清楚，易于构造，但是在实际生活中，用连接运算表达的查询尽可能采用连接查询。

  子查询的查询条件不依赖父查询，这类查询称为不相关子查询。不相关子查询是较简单的一类子查询。如果子查询的查询条件依赖父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询。

2.带有比较运算符的子查询

 
2.带有比较运算符的子查询

  带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层返回的是单个值时，可以使用>,<,=,>=,<=,!=或<>等比较运算符。

【例 3.57】 找出每个学生超过（或等于）他自己选修课程平均成绩的课程号。

  select Sno,Cno
  from SC x
  where Grade >=(select avg(Grade)
				from SC y
				where y.Sno=x.Sno
				);				

求解相关子查询和求解不相关子查询不同，内层查询由于与外层查询有关，所以必须反复求值。

  1.从外层查询中取出SC的一个元组 x ，将元组 x 的Sno值传递给内层查询。

  2.执行内层查询，可以得到一个值，用这个值替代内层查询，代入到外层查询中。

  3.执行查询，得到部分结果

然后外层查询取出下一个元祖重复上述操作，直到外层的 SC 元祖全部处理完毕。（类似两层for循环）

3.带有ANY（SOME）或 ALL 谓词的子查询。

 
3.带有ANY（SOME）或 ALL 谓词的子查询

  子查询返回单值时可以使用比较运算符，但返回多值时要用 ANY 或 ALL 谓词修饰符。而使用 ANY 或 ALL 谓词时必须同时使用比较运算符。

  使用谓词 any 与 all 必须同时使用 比较运算符。

【例 3.58】 査询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

  select Sname,Sage
  from Student
  where Sage<any(select Sage
 			   from Student
			   where Sdep='CS')
	    and Sdep<>'CS';

根据题意，本例也可以使用聚集函数来实现。

  select Sname,Sage
  from Student
  where Sage <
		  (select max(Sage)
		   from Student
		   where Sdep='CS')
	    and Sdep <>'CS';

  子查询返回是一个集合，因为是多值。

【例 3.59】 査询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

  select Sname,Sage
  from Student
  where Sage < all(select Sage
  			   from Student
			   where Sdep='CS')
	    and Sdep<>'CS';

同理，本例也可以使用聚集函数来实现。

  select Sname,Sage
  from Student 
  where Sage <
		     (select min(Sage)
		      from Student 
			  where Sdep='CS')
	    and Sdep <>'CS';

对应关系如下：

  =ANY等价于 IN 谓词，< ANY 等价于 < MAX，<>ALL 等价于 NOT IN 谓词，<ALL 等价于<MIN，等等。

4.带有 EXISTS 谓词的子查询。

 
4.带有 EXISTS 谓词的子查询

  EXISTS 代表存在量词 ∃。带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值 “true” 和 “false” 。

【例 3.61】 査询所有选修了 1号课程的学生姓名。

  本査询涉及Student和SC表。可以在Student中依次取每个元组的Sno值，用此值去检査SC表。若SC中存在这样的元组，其Sno值等于Student.Sno值，并且其Cno = T,则取此Student.Sname送入结果表。

select Sname
from Student
where exists
		(select *
		 from SC
		 where Sno=Student.Sno and Cno='1');
		

  使用存在量词exists后,若内层査询结果非空，则外层的where子句返回真值，否则返回假值。

  由EXISTS引出的子査询，其目标列表达式通常都用*,因为带exists的子査询只返回真值或假值，给出列名无实际意义。

【例 3.61】 査询没有选修1号课程的学生姓名。

select Sname
from Student
where not exists
		(select *
		 from SC
		 where Sno=Student.Sno and Cno='1');

  所有带有 IN 谓词，比较运算符，ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换。并且由于EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要具体值，所以效率并不一定低于不相关子查询。

【例 3.62】 査询选修了全部课程的学生姓名。

  SQL中没有全称量词(fbrall),但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

  ( ∀ x ) P ≡ ( ∃ x ( ¬ P ) )

  select Sname
  from Student
  where not exists
		(select *
		 from Course
		 where not exists
				(select *
				 from SC
				 where Sno=Student.Sno and
				       Cno=Course.Cno));

因为是查找选修所有课程的同学的姓名，但是表中的数据较少，所以没有数据。。。

【例 3.63】 査询至少选修了学生201215122选修的全部课程的学生号码。

  本查询可以用逻辑蕴涵来表达:査询学号为x的学生，对所有的课程y,只要201215122学生选修了课程y,则x也选修了 y。形式化表示如下：

用p表示谓词“学生201215122选修了课程y”
用q表示谓词“学生x选修了课程y” ，则上述査询为
（∀ y） ( p → q )

SQL语言中没有蕴涵(implication)逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为
p → q ≡ ¬ p ∨ q

该査询可以转换为如下等价形式：
( ∀ y ) p → q ≡ ¬ ( ∃ y ( ¬ ( ¬ p ∨ q ) ) ) ≡ ¬ ∃ y ( p ∧ ¬ q )

它所表达的语义为：不存在这样的课程y,学生201215122选修了 y,而学生x没有选。

select distinct Sno
from SC SCX
where not exists
		(select *
		 from SC SCY
		 where SCY.Sno='201215122' and
		       not exists
			   (select *
			    from SC SCZ
				where SCZ.Sno=SCX.Sno and
				      SCZ.Cno=SCY.Cno));

总结

  注意区分任意一个和所有，跟平时的有点不太一样，后面的使用 EXISTS 去实现全程量词和逻辑蕴涵可能会有一点比较难理解，还得多记忆，多复习。