在SQL的子查询中,如果子查询返回多个行时,我们知道可以使用IN关键字来查找字段值属某一组值的行,同样,可以在查询中使用EXISTS、 ALL、 ANY (或some)等运算符。
其中,in()后面的子查询是返回结果集的,换句话说执行次序和exists()不一样,子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出,反之则不输出。如:有一张bbc表,需要找出各个地区人口最大的国家:
select name,region,population from bbc
where population in (select max(population) from bbc group by region ) order by population
exists()后面总是个跟一个子查询(相关的或非相关的都行),EXISTS的左边不用接列名,用来测试子查询是否返回任何结果, 他只要子查询返回了行,EXISTS的值就为真,其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出。如:有两张表为studentexam表和student表,需要查询返回任何一次考试中分数低于40分的学生的studentid和姓名:
select studentid ,name from student s
where exists (select studentid from studentexam e where mark <40 and e.studentid=s.studentid)
但是,通常情况下采用exists要比in效率高。
而 ALL、 ANY (或some)需要配合关系运算符来使用,他们的左边要联接列名,右边是子查询。All对所有数据都满足条件,整个条件才成立 ;.Any只要有任一条数据满足条件,整个条件就成立;Some和Any一样表示的限制相同。
一般情况下:
(1)=ANY 运算符与 IN 等效。如:在BBC表中,要查找出和india(印度)、iran(伊朗)所在地区的所有国家的所有信息:
select * from bbc
where region = any (select region from bbc where name='india'or name='iran')
select * from bbc
where regionin (select region from bbc where name='india'or name='iran')
(2)>any运算符与>min等效。
(3)<any运算符与<max等效。如在两张表enrollment表和studentexam表中,要找出总分小于任何一次考试的分数的一半的学生的studentid和总分:
select studentid,grade from enrollment e
where grade< any(select mark/2 from studentexam s where s.studentid=e.studentid)
select studentid,grade from enrollment e
where grade< (select max(mark/2 )from studentexam s where s.studentid=e.studentid)
(4)>all运算符和>max等效。如BBC表中,要查找出gdp比任何欧洲国家都多的国家:
select name from bbc
where gdp>(selectmax(gdp) from bbc where region='Europe')
select name from bbc
where gdp>all (select gdp from bbc where region='Europe')
(5)<all运算符和<min等效。