MySQL子查询之where后的列子查询和行子查询

where后面的列子查询(多行子查询)

首先来看一下多行操作符

in/not in:等于列表中的任意一个

a in(10,20,30); 可以替换 a=10 or a=20 or a=30;

any/some:和子查询返回的某一个值比较

a > any(10,20,30); 可以替换 a > min(10,20,30);

all:和子查询返回的所有值比较

a > all(10,20,30); 可以替换 a > max(10,20,30);

a >10 and a>20 and a>30

案例1：

返回location_id是14000或17000的部门中的所有员工的名字

select last_name from employees
where department_id in (select department_id from departments
where location_id in (14000,17000));

案例2：

查询其他工种中比job_id为'IT_PROG'的员工某一工资低的员工的员工号,姓名,job_id和salary

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

where后面的行子查询(一行多列)

案例：

查询员工编号最小并且工资最高的员工信息

select * from employees
where
employee_id = (select min(employee_id) from employees)
and
salary = (select max(salary) from employees);

行子查询的写法:

下面是行子查询的写法（用的很少,了解就可以）

select * from employees
where (employee_id,salary)=(
select min(employee_id) , max(salary) from employees
);