【MySQL】多行子查询
1.多行子查询
也称为集合比较子查询,内查询返回多行,使用多行比较操作符。
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 和子查询返回的某一个值比较,需要和单行比较操作符一起使用 |
ALL | 和子查询返回的所有值比较,需要和单行比较操作符一起使用 |
SOME | 实际上是ANY的别名,作用相同,一般使用ANY |
示例1:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
示例2:返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及
salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
示例3:查询平均工资最低的部门id
方式一
先查部门平均工资,然后查其中最低的,接着取部门平均工资中相等的部门id。注意起别名
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(salary)
FROM(
SELECT AVG(salary) AS salary
FROM employees
GROUP BY department_id
) AS dept_avg_sal
);
方式二:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
);
空值问题
查询结果数据为空
SELECT last_name
FROM employees
WHERE employee_id NOT IN(
SELECT manager_id
FROM employees
);