子查询返回单值时可以用比较运算符,而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义如下:
>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
>=ANY 大于等于子查询结果中的某个值
>=ALL 大于等于子查询结果中的所有值
<=ANY 小于等于子查询结果中的某个值
<=ALL 小于等于子查询结果中的所有值
=ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值(通常没有实际意义)
!=(或<>)ANY 不等于子查询结果中的某个值
!=(或<>)ALL 不等于子查询结果中的任何一个值
例39 查询其他系中比信息系某一学生年龄小的学生姓名和年龄
select sname,sage
from student
where sage <any (select sage
from student
where sdept='IS')
AND sdept<>'IS'
DBMS执行此查询时,首先处理子查询,找出IS系中所有学生的年龄,构成一个集合(19,18)。然后处理父查询,找所有不是IS系且年龄小于19或18的学生。
本查询也可以用集函数来实现。首用用子查询找了IS系中最大年龄(19),然后在父查询中查所有非IS且年龄小于19岁的学生姓名及年龄。SQL语句如下:
select sname,sage
from student
where sage <
(select max(sage)
from student
where sdept='IS')
AND sdept<>'IS'
例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄
select sname,sage
from student
where sage <all (select sage
from student
where sdept='IS')
AND sdept<>'IS'
查询结果为空表,本查询同样也可以用集函数来实现。其SQL语句如下:
select sname,sage
from student
where sage <
(select min(sage)
from student
where sdept='IS')
AND sdept<>'IS'
事实上,用集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY与ALL与集函数的对应关系如下:
= | <>或!= | < | <= | > | >= | |
ANY | in | <max | <=max | >min | >=min | |
ALL | not in | <min | <=min | >max | >=max |