列子查询（多行子查询）

多行子查询
返回多行。
使用多行比较操作符。
操作符 含义
IN/NOT IN 等于列表中的任意一个
ANY|SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
案例1：返回location_id是1400或1700的部门中的所有员工姓名
1.查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700) ;

2.查询员工姓名，要求部门号是1. 列表中的某一个

SELECT last_name
FROM employees
WHERE department_id IN(
						SELECT DISTINCT department_id
						FROM departments
						WHERE location_id IN(1400,1700) ;
						);
或
SELECT last_name
FROM employees
WHERE department_id =ANY(
						SELECT DISTINCT department_id
						FROM departments
						WHERE location_id IN(1400,1700) ;
						);
不是1. 中的某一个
SELECT last_name
FROM employees
WHERE department_id NOT IN(
						SELECT DISTINCT department_id
						FROM departments
						WHERE location_id IN(1400,1700) ;
						);
或
SELECT last_name
FROM employees
WHERE department_id <>ALL(
						SELECT DISTINCT department_id
						FROM departments
						WHERE location_id IN(1400,1700) ;
						);

案例2：返回其它工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
1.查询出job_id为’IT_PROG’部门的员工的工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

2.查询员工的工号、姓名、job_id 以及salary，要求是salary比1. 低

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
					SELECT DISTINCT salary
					FROM employees
					WHERE job_id = 'IT_PROG';
					)
		AND job_id<>'IT_PROG';

案例3：返回其它部门中比job_id为’IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
					SELECT DISTINCT salary
					FROM employees
					WHERE job_id = 'IT_PROG';
					)
		AND job_id<>'IT_PROG';
或

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
					SELECT MIN(salary)
					FROM employees
					WHERE job_id = 'IT_PROG';
					)
		AND job_id<>'IT_PROG';