- 谁的工资比Abel高
select last_name,salary from employees where salary > (select salary from employees where last_name = 'Abel');
查询员工名为Chen的manager的信息
select last_name,salary from employees where employee_id = (select manager_id from employees where last_name = 'Chen');
- 单行子查询
只返回一行数据,使用单行比较操作符
操作符 | 含义 |
---|---|
= | 等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于 |
返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary from employees where salary = (select min(salary) from employees)
查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary) from employees group by department_id having min(salary) > (select min (salary) from employees where department_id =50)
- 多行子查询
返回多行,使用多行比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
select employee_id, last_name,job_id,salary from employees where job_id <> 'IT_PROG' and salary < any (select salary from employees where job_id = 'IT_PROG')
操作符为>时,比子查询返回的最小值的大即可成立,
操作符为<时,比子查询返回的最大值的小即可成立,
操作符为>时,比子查询返回的最大值的大才可成立,
操作符为<时,比子查询返回的最小值的小才可成立,
子查询返回null,sql不会报错