单行比较示例
谁的薪水比Abel高
SELECT last_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
注:
单行比较必须对应单行子查询(返回单一结果值的查询);比如= , >
多行比较必须对应多行子查询(返回一个数据集合的查询);比如 IN , > ANY, > ALL 等
谁的薪水最低
select employee_id, last_name
from employees
where salary = (select min(salary) from employees)
多行比较
小于任何一个IT部门人员薪水的人员
SELECT employee_id
,last_name
,job_id
,salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
注:<ANY 小于集合里任何一个值
小于所有IT部门人员薪水的人员
SELECT employee_id
,last_name
,job_id
,salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
注:<ALL 小于集合里所有的值