mysql基础_03_子查询

一、概述

1. 含义

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

2. 分类

2.1 按子查询出现的位置：

• select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：
  • 标量子查询（单行）
  • 列子查询 （多行）
  • 行子查询
• exists后面（相关子查询）
  • 表子查询

2.2 按结果集的行列数不同：

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

3. 特点

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配着单行操作符使用 ( > < >= <= = <> )
• 列子查询，一般搭配着多行操作符使用 (in、any/some、all )
• 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

二、 标量子查询

• 结果集只有一行一列

1. where后面

例：谁的工资比 Abel 高?

	SELECT *
	FROM employees
	WHERE salary>(
		SELECT salary		#查询到的结果只能有一行一列
		FROM employees
		WHERE last_name = 'Abel'
	);

• 子查询的结果
  

2. select后面

查询每个部门的员工个数

	SELECT d.*,(
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.`department_id`
	) 个数
	FROM departments d;

• 查询的结果
• 

三、列子查询 （多行子查询）

• 子查询返回 一列多行
• 多行比较操作符

操作符	含义
IN / NOT IN	等于列表中的任意一个
ANY / SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

• 一般 ANY/SOME/ALL 都可以使用其他方式代替，比如 a > all (10,20,30) 可以换成 a> max(10,20,30)

1. where后面

例：返回location_id是1400或1700的部门中的所有员工姓名 和部门编号

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

子查询结果

查询结果

四、 行子查询

• 结果集有一行多列

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

	SELECT * 
	FROM employees
	WHERE (employee_id,salary)=(
		SELECT MIN(employee_id),MAX(salary)
		FROM employees
	);

	#等价于下面
	SELECT *
	FROM employees
	WHERE employee_id=(
		SELECT MIN(employee_id)
		FROM employees
	)AND salary=(
		SELECT MAX(salary)
		FROM employees
	);

子查询结果

查询结果

五、表子查询

• 跟在 from 后面
• 将查询的结果充当一个数据源 的表 ,这个表必须起别名。

例： 查询每个部门的平均工资的工资等级

	SELECT j.* , department_id ,avg_salary
	FROM 	(
				SELECT AVG(salary) avg_salary,department_id
				FROM employees e
				GROUP BY department_id
			) ag_dep		# 表必须起别名
	JOIN job_grades j
	ON ag_dep.avg_salary BETWEEN lowest_sal AND highest_sal;

子查询结果

查询结果

六、 相关子查询 exists

• exists 判断查询结果是否 存在 ，查询结果是否为空
• 可以使用 IN 代替

---

存在

SELECT EXISTS(
	SELECT *
	FROM employees
) result

---

不存在

SELECT EXISTS(
	SELECT *
	FROM employees
	WHERE last_name = '111'
) result

---

案例：

查询所有有员工的部门名

# 使用 EXISTS
SELECT department_name
FROM departments d
WHERE EXISTS(
		SELECT *
		FROM employees e
		WHERE d.department_id = e.department_id
);
# 使用 IN
SELECT department_name
FROM departments
WHERE department_id IN (
		SELECT DISTINCT department_id
		FROM employees
)