SQL 子查询

1、单行子查询
--返回job_id与141号员工相同，salary比143
号员工多的员工的姓名、job_id、工资


select last_name,job_id,salary
from employees
where job_id=(
select job_id
from employees
where employees_id=141
)
and salary>(
select salary
from employees
where employees_id=143
)






2、多行子查询
返回其他部门中比job_id为‘IT_PROG’
部门任一工资低的员工的
员工号、姓名、job_id、salary




select employees_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'
)




select employees_id,last_name,
job_id,salary
from employees
where job_id<>'IT_PROG' 
//比子查询返回所有记录都小
and salary<all(
select salary
from employees
where job_id='IT_PROG'


)