多行子查询返回多行记录 对多行子查询只能使用多行记录比较运算符
运算符 含义
IN 等于列表中的任何一个
ANY 和子查询返回的任意一个值比较
ALL 和子查询返回的所有值比较 举例:
select * from emp
where sal > any(select avg(sal) from emp group by deptno);
select * from emp
where sal > all(select avg(sal) from emp group by deptno);
select * from emp
where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH');
运算符 含义
IN 等于列表中的任何一个
ANY 和子查询返回的任意一个值比较
ALL 和子查询返回的所有值比较 举例:
select * from emp
where sal > any(select avg(sal) from emp group by deptno);
select * from emp
where sal > all(select avg(sal) from emp group by deptno);
select * from emp
where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH');