05—— DQL语言基础：子查询

一、子查询基础

1、含义：

• 出现在其它语句中的 select 语句，称为子查询或内查询。
• 内部嵌套其它 select 语句的查询，称为外查询或主查询

2、分类：

（1）、按子查询出现的位置：

• select 后面【只支持标量子查询】
• from 后面【支持表子查询】
• where 或 having 后面【标量子查询、列子查询、行子查询】
• exists 后面（相关子查询）【表子查询】

（2）、按结果集的行列数不同：

• 标量子查询（结果集中只有一行一列）
• 列子查询（结果集中只有一列多行）
• 行子查询（结果集中有一行多列）
• 表子查询（结果集一般为多行多列）

---

二、应用：子查询位于 where 、having 后面

1、标量子查询

• 子查询放在小括号后面
• 标量子查询一般搭配单行操作符（如：>、<、>=、<=、=、<>）
• 列子查询一般搭配多行操作符（如：in 、any 、some、 all）
• 子查询会优先于主查询执行

（1）、示例1：谁的工资比 Abel 高

# 标量子查询，因为where后的结果为一个值
SELECT salary,last_name
FROM employees
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');

    

（2）、示例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名、job_id和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id 
	FROM employees 
	WHERE employee_id=141)
AND salary>(
	SELECT salary 
	FROM employees 
	WHERE employee_id=143);

   

（3）、示例3：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);

  

2、列子查询（一列多行）

多行子查询需要搭配多行操作符，如下图：

  

【注】any 是指其中任意一个，只要有一个符合即可，常对应 max() 函数；all 是指所有的都必须符合，常对应 min() 函数

（1）、示例1、返回 location_id 是1400或1700的部门中所有员工姓名

SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id 
	FROM departments 
	WHERE location_id=1400 OR location_id=1700);

    

（2）、示例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';
#或者条件为如下：
#where salary<(
#select max(salary) from employees where job_id='IT_PROG')
#and job_id<>'IT_PROG';

   

（3）、示例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 salary<(
#select min(salary) from employees where job_id='IT_PROG')
#and job_id<>'IT_PROG';

  

3、行子查询（结果为一行多列或多行多列）

示例1：查询员工编号最小、并且工资最高的员工信息

# 使用行子查询的条件是：筛选字段为同一操作符（如都是>、< ）
SELECT *
FROM employees
WHERE (employee_id,salary)=
	(SELECT MIN(employee_id),MAX(salary)
	FROM employees);

# 或者是之前的方法：加两个筛选条件
SELECT *
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees)
AND employee_id=(SELECT MIN(employee_id) FROM employees);

---

三、应用：子查询位于 select 后面

【注意】：select 后的子查询只能是一行一列

示例1、查询每个部门的员工个数（部门名和员工位于两个不同的表中）

SELECT d.department_name,(
	SELECT COUNT(*) 
	FROM employees e 
	WHERE e.department_id=d.department_id) Num
FROM departments d;

     

---

四、子查询位于 from 后面

• 将子查询的结果充当一张表，必须要为其起别名，否则无法找到该表

1、查询每个部门平均工资的等级

SELECT ag_dep.*, g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

---

五、子查询位于 exists 后面

1、语法：

（1）、exists 用于对后面的子查询结果是否为空进行判断。若后面的子查询为空，则exists 返回0

# 由于子查询存在则返回1，否则返回0
SELECT EXISTS (SELECT employee_id FROM employees);