SQL中带SOME、ANY、ALL的子查询
准备两个表:
–T1(2,3)
–T2(1,2,3,4)
测试
-
“>ALL” : 父查询中的结果集大于子查询中每一个值(大于子查询结果集中的最大值),则为真;
select * from T2
where n > ALL(select n from T1)
等价于
select * from T2
where n>(select max(n) from T1)
结果显示:
4 -
“<ALL” : 父查询中的结果集小于子查询中每一个值(小于子查询结果集中的最小值),则为真;
select * from T2
where n < ALL(select n from T1)
等价于
select * from T2
where n<(select min(n) from T1)
结果显示:
1 -
“<>ALL”:作用于NOT IN相同;
select * from T2
where n <> ALL(select n from T1)
等价于
select * from T2
where n NOT IN (select n from T1)
结果显示:
1,4 -
“>ANY”:父查询中的结果集大于子查询中任意一个值(大于子查询结果集中的最小值),则为真;
select * from T2
where n > ANY(select n from T1)
等价于
select * from T2
where n>(select min(n) from T1)
结果显示:
3,4 -
“=ANY”:与子查询IN相同,父查询条件表达式存在于子查询的结果集中里;
select * from T2
where n = ANY(select n from T1)
等价于
select * from T2
where n IN (select n from T1)
结果显示:
2,3 -
“<ANY”:父查询中的结果集小于子查询中任意一个值(小于子查询结果集中的最大值),则为真;
select * from T2
where n < ANY(select n from T1)
等价于
select * from T2
where n<(select max(n) from T1)
结果显示:
1,2