什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从
MySQL 4.1
开始引入；

SQL
中子查询的使用大大增强了
SELECT
查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

一个实际场景

求员工表中工资比 Abel这个员工工资高的其他员工

现有解决方式

1、分步骤查询

SELECT salary FROM employees WHERE last_name = 'Abel'; 


SELECT last_name,salary FROM employees WHERE salary > 11000;

2、自连接

SELECT e2.last_name,e2.salary FROM employees e1,employees e2 WHERE e1.last_name = 'Abel' AND e1.`salary` < e2.`salary`

3、子查询

SELECT salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );

子查询的基本使用

子查询的基本语法结构：

子查询（内查询）在主查询之前一次执行完成；
子查询的结果被主查询（外查询）使用；

注意事项

子查询要包含在括号内；
将子查询放在比较条件的右侧；
单行操作符对应单行子查询，多行操作符对应多行子查询；

子查询的分类

我们按内查询的结果返回一条还是多条记录，将子查询分为
单行子查询
、
多行子查询

1、单行子查询

2、多行子查询

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询；

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询；
同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询；

单行子查询

单行比较操作符

代码示例

查询工资大于
149
号员工工资的员工的信息

返回
job_id
与
141
号员工相同，
salary
比
143
号员工多的员工姓名，
job_id
和工资

返回公司工资最少的员工的
last_name,job_id
和
salary

查询与
141
号或
174
号员工的
manager_id
和
department_id
相同的其他员工
employee_id
，

manager_id
，
department_id

实现方式
1
：不成对比较

SELECT employee_id, manager_id, department_id 
FROM employees
 WHERE manager_id IN
 (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) 

AND department_id IN 
(SELECT department_id FROM employees WHERE employee_id IN (174,141)) 

AND employee_id NOT IN(174,141)

实现方式
2
：成对比较

SELECT
	employee_id,
	manager_id,
	department_id
FROM
	employees
WHERE
	(manager_id, department_id) IN (
		SELECT
			manager_id,
			department_id
		FROM
			employees
		WHERE
			employee_id IN (141, 174)
	)
AND employee_id NOT IN (141, 174);

查询最低工资大于
50
号部门最低工资的部门
id
和其最低工资

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

CASE中的子查询

在CASE表达式中使用单列子查询：

显式员工的
employee_id,last_name
和
location
。其中，若员工
department_id
与
location_id
为
1800 的department_id
相同，则
location
为
’Canada’
，其余则为
’USA’

SELECT
	employee_id,
	last_name,
	(
		CASE department_id
		WHEN (
			SELECT
				department_id
			FROM
				departments
			WHERE
				location_id = 1800
		) THEN
			'Canada'
		ELSE
			'USA'
		END
	) location
FROM
	employees;

多行子查询

也称为集合比较子查询；
内查询返回多行；
使用多行比较操作符；

多行比较操作符

返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

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

查询平均工资最低的部门id

方式1：

SELECT
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	AVG(salary) = (
		SELECT
			MIN(avg_sal)
		FROM
			(
				SELECT
					AVG(salary) avg_sal
				FROM
					employees
				GROUP BY
					department_id
			) dept_avg_sal
	)

方式2：

SELECT
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	AVG(salary) <= ALL (
		SELECT
			AVG(salary) avg_sal
		FROM
			employees
		GROUP BY
			department_id
	)

EXISTS 与 NOT EXISTS关键字

1、关联子查询通常也会和
EXISTS
操作符一起来使用，用来检查在子查询中是否存在满足条件的行；

2、
如果在子查询中不存在满足条件的行：
条件返回 FALSE，继续在子查询中查找；

3、
如果在子查询中存在满足条件的行：
不在子查询中继续查找，
条件返回
TRUE；

4、
NOT EXISTS
关键字表示如果不存在某种条件，则返回
TRUE
，否则返回
FALSE；

查询公司管理者的
employee_id
，
last_name
，
job_id
，
department_id
信息

方式1：

SELECT
	employee_id,
	last_name,
	job_id,
	department_id
FROM
	employees e1
WHERE
	EXISTS (
		SELECT
			*
		FROM
			employees e2
		WHERE
			e2.manager_id = e1.employee_id
	);

方式2：自连接

SELECT DISTINCT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id
FROM
	employees e1
JOIN employees e2
WHERE
	e1.employee_id = e2.manager_id;

方式3：

SELECT DISTINCT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id
FROM
	employees e1
WHERE
	e1.employee_id IN (
		SELECT DISTINCT
			manager_id
		FROM
			employees
	);

查询departments表中，不存在于employees表中的部门的department_iddepartment_name

SELECT
	department_id,
	department_name
FROM
	departments d
WHERE
	NOT EXISTS (
		SELECT
			'X'
		FROM
			employees
		WHERE
			department_id = d.department_id
	);