多行子查询

多行子查询：

• 也称为集合比较子查询

• 内查询返回多行

• 使用多行比较操作符

#多行子查询
# 多行子查询的操作符：IN ANY ALL SOME(同ANY)
​
#IN：
SELECT employee_id,last_name
FROM employees
WHERE salary IN(
              SELECT MIN(salary)
                            FROM employees
                            GROUP BY department_id
                            )

#ANY / ALL:
#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
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'
                             );
​
#返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
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'
                );

#查询平均工资最低的部门id
#mysql 中聚合函数是不能嵌套使用的
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
                   SELECT MIN(avg_sal)
                   FROM (
                        SELECT AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id
                              ) t_dept_avg_sal
                                     );
#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <=ALL(
                        SELECT AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id 
                        );

                                     
#空值问题：