Oracle ALL、ANY关键字用法

ALL:表示条件满足于所有子查询结果的数据

例如：查询工资大于所有职位是 ‘ST_CLERK’ 的员工工资的员工姓名，工资

先看下’ST_CLERK’职位的所有员工与工资

select salary from employees where job_id = 'ST_CLERK');

运行结果：

即此题可理解为
select salary from employees
where salary > 3500 and salary >3100 and salary >2600 and salary > 2500
而使用ALL关键字既可以表示条件满足于子查询所有结果

select last_name, job_id, salary
from employees
where salary > ALL(select salary
    from employees
    where job_id = 'ST_CLERK');

ANY:表示条件满足于任意子查询结果的数据

例如：查询工资大于职位是 ‘ST_CLERK’ 的任意一名员工工资的员工姓名，工资

即此题可理解为
select salary from employees
where salary > 3500 or salary >3100 or salary >2600 or salary > 2500
而使用ANY关键字既可以表示条件满足于子查询任意结果

select last_name, job_id, salary
from employees
where salary > ANY(select salary
    from employees
    where job_id = 'ST_CLERK');