SQL学习笔记14——子查询

        当过滤条件是未知的时候，需要用到子查询。即先查询过滤条件的值，再进行外层查询。

1.单行子查询

        子查询返回一条结果，常用>,<,=>,=<,!=,=等运算符。

例1：查询工资比Abel高的员工姓名及工资。

select last_name,salary
from employees
where salary > (
                select salary
                from employees
                where last_name = 'Abel'
                )

例2：查询员工Chen的领导的个人信息。

select *
from employees
where employee_id = (
                    select manager_id
                    from employees
                    where last_name = 'Chen'
                    )

例3：查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资。

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

例4：查询最低工资大于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
                    )

2.多行子查询

        子查询返回多条结果，常用in，any，all等运算符。

例1：查询其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary。

        若IT_PROG部门的工资区间为3000—6000，则此题需其他部门员工工资至少低于6000。

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

 例1：查询其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salary。

        若IT_PROG部门的工资区间为3000—6000，则此题需其他部门员工工资低于3000。

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

3.联表子查询

 例1：查询平均工资最低的部门信息和该部门的平均工资。

select d.*,(select avg(salary) from employees where department_id = d.department_id )
from departments d
where department_id = (
                        select department_id
                        from employees
                        group by department_id
                        having avg(salary) = (
                                              select min(avg(salary)) 
                                              from employees 
                                              group by department_id
                                              )
                      )

 例2：查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资。

select employee_id,last_name,salary
from employees e1
where salary > (
                select avg(salary) 
                from employees e2 
                where e1.department_id = e2.department_id 
                group by department_id
                )