一、子查询(作为条件判断)
SELECT 列名 FROM 表名 Where 条件(子查询结构)
例题1
#查询Lex的工资
SELECT salary FROM t_employees WHERE first_name = 'Lex';
#查询工资大于45000的
SELECT salary FROM t_employees WHERE salary >= 10000;
#查询工资大于lex员工的所有人
SELECT salary FROM t_employees WHERE salary >= (SELECT salary FROM t_employees WHERE first_name = 'Lex');
二、作为枚举查询条件
SELECT 列名 FROM 表名 Where 列名 in(子查询结果);
例题2
查询与名为Liu同一部门的员工信息
#1. 查询‘Liu’所在部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE first_name = 'Liu';
#2. 再查询90、30号部门的员工信息
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE department_id IN (90,30);
#3. 合并
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE department_id IN (SELECT department_id FROM t_employees WHERE first_name = 'Liu');
三、子查询结构集形式为多行单列时可以使用ANY或ALL关键字
例题3
#查询90部门所有人的信息(多行多列)
SELECT salary FROM t_employees WHERE department_id = 90;
#查询高于90部门所有人的工资的员工信息(高于所有)
SELECT *FROM t_employees WHERE salary >ALL(SELECT salary FROM t_employees WHERE department_id = 90);
#查询高于90部门的工资的员工信息(高于部分人)
SELECT *FROM t_employees WHERE salary >ANY(SELECT salary FROM t_employees WHERE department_id = 90);
四、子查询(作为一张表)
例题4
查询员工表中工资排名前5名的员工信息
#思路
#1 先对所有员工的工资进行降序排序(排序后的临时表)
SELECT employee_id,first_name,salary
FROM t_employees
ORDER BY salary DESC;
#再查询临时表中前5行员工信息
SELECT employee_id,first_name,salary
FROM 临时表
LIMIT 0,5
# 合并
SELECT employee_id,first_name,salary
FROM (SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary DESC) AS temp
LIMIT 0,5;