相关子查询

相关子查询：

即在查询中除了用到子查询外，还用到了表之间的连接(即多表查询)

以下有几个例子：

#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
select employee_id ,last_name ,salary 
from employees e 
where salary >= (
                select avg(salary)
                from employees e2 
                group by department_id 
                having department_id in (
                                        select department_id 
                                        from employees e3 
                                        where e.employee_id = e3.employee_id 
                                        )
                );
            

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
select *
from departments d 
where 5 <= (
            select count(*)
            from employees e 
            where e.department_id = d.department_id 
            );

#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
select *
from locations l 
where 2 < (
            select count(*)
            from departments d 
            where l.location_id = d.location_id 
            );