1，也称为集合比较子查询
2，内查询返回多行
3，使用多行比较操作符

多行比较操作符

多行子查询的操作符： 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
            );

空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
            SELECT manager_id
            FROM employees
            );