MySQL where后面的列子查询使用_where后面的条件在列表中

多行子查询

• 返回多行
• 使用多行操作比较操作符

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号

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

#②查询员工姓名，要求部门号是①列表中的某一个

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)
);

注意：not in 等价于 <> ALL​​​​​​​

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_i <> ALL(
  SELECT DISTINCT department_id
  FROM departments
  WHERE location_id IN(1400,1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

#①查询job_id为‘IT_PROG’部门任一工资​​​​​​​

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个​​​​​​​

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

#或​​​​​​​

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

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

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

#或​​​​​​​

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