什么是子查询

子查询，是指一个查询语句嵌套在另一个查询语句内的查询，因此也称为“嵌套查询”。
比如， 要查询谁的工资比Abel高？

# 谁的工资比Abel高
# 方式1：两次查询
SELECT salary 
FROM employees 
WHERE last_name = 'Abel'; -- 返回11000

SELECT last_name,salary
FROM employees
WHERE salary > 11000; -- 返回10行记录

# 方式2：自连接
SELECT e1.salary,e1.last_name
FROM employees e1,employees e2
WHERE e1.salary > e2.salary
AND e2.last_name = 'Abel'; -- 返回10行记录

# 方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
				SELECT salary
				FROM employees
				WHERE last_name = 'Abel'
				) -- 返回10行记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

单行子查询和多行子查询

按照子查询返回的记录行数，分为单行子查询和多行子查询。

单行子查询

单行子查询，即子查询返回单行记录。
单行子查询使用的比较操作符有：>，>=，<，<=，=，<>。

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

# 查询工资大于149号员工工资的员工信息
SELECT last_name,employee_id,salary
FROM employees
WHERE salary > (
				SELECT salary
				FROM employees
				WHERE employee_id = 149
				)
1
2
3
4
5
6
7
8

2. 返回job_id与141号员工相同，salary比143号员工高的员工姓名、job_id和工资。

# 返回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
				);
1
2
3
4
5
6
7
8
9
10
11
12
13

3. 返回公司工资最少的员工的last_name、job_id和salary。

# 返回公司工资最少的员工的last_name、job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
				SELECT MIN(salary)
				FROM employees
				)
1
2
3
4
5
6
7

4. 查询与141号的manager_id和department_id相同的其他员工的employee_id、manager_id、department_id。

# 查询与141号的manager_id和department_id相同的其他员工的employee_id、manager_id、department_id
# 第一种写法
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
					SELECT manager_id
					FROM employees
					WHERE employee_id = 141
					)
AND manager_id = (
					SELECT manager_id
					FROM employees
					WHERE employee_id = 141
					)
AND employee_id <> 141; -- 返回7行记录

# 第二种写法
SELECT employee_id,manager_id,department_id
FROM employees
WHERE(manager_id,department_id) = (
									SELECT manager_id,department_id
									FROM employees
									WHERE employee_id = 141
									)
AND employee_id <> 141; -- 返回7行记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
						SELECT MIN(salary)
						FROM employees
						WHERE department_id = 50
						) -- 返回10行记录
1
2
3
4
5
6
7
8
9
10

6. 显示员工的employee_id、last_name和location。其中，如果员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

#显示员工的employee_id、last_name和location。其中，如果员工department_id与location_id为1800的department_id相同，则location为'Canada'，其余则为'USA'。

# 第一步
SELECT department_id
FROM departments
WHERE location_id = 1800; -- 返回20
# 第二步
SELECT employee_id,last_name,CASE department_id WHEN 20 THEN 'Canada'
												ELSE 'USA' END "location"
FROM employees; -- 返回107行记录

# 汇总
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; -- 返回107行记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

多行子查询

多行子查询，即子查询返回多行记录。
多行子查询使用的比较操作符有：IN、ANY、ALL、SOME。

• IN，等于列表中的任意一个。
• ANY，需要和单行比较操作符一起使用，和子查询返回的某一个值比较。
• ALL，需要和单行比较操作符一起使用，和子查询返回的所有值比较。
• SOME，ANY的别名，作用相同，但常用ANY。

1. 返回其他job_id中比job_id为’IT_PROG’部门任意一工资低的employee_id、last_name、job_id、salary。

# 返回其他job_id中比job_id为'IT_PROG'部门任意一工资低的employee_id、last_name、job_id、salary。
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE job_id <> 'IT_PROG'
AND  salary < ANY (
					SELECT salary
					FROM employees
					WHERE job_id = 'IT_PROG'
					); -- 返回76行记录
1
2
3
4
5
6
7
8
9

2. 返回其他job_id中比job_id为’IT_PROG’部门所有工资低的employee_id、last_name、job_id、salary。

# 返回其他job_id中比job_id为'IT_PROG'部门所有工资低的employee_id、last_name、job_id、salary。
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE job_id <> 'IT_PROG'
AND  salary < ALL (
					SELECT salary
					FROM employees
					WHERE job_id = 'IT_PROG'
					); -- 返回44行记录
1
2
3
4
5
6
7
8
9

3. 查询平均工资最低的部门。

# 查询平均工资最低的部门
# 方式1
SELECT department_id,AVG(salary)
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
						) t_dep_avg_sal
					);
/*
返回
department_id AVG(salary)
50	          3475.555556
*/

# 方式2
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
							SELECT AVG(salary)
							FROM employees
							GROUP BY department_id
							);
/*
返回
department_id AVG(salary)
50	          3475.555556
*/
														
# 方式3
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1;
/*
返回
department_id avg_sal
50	          3475.555556
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

4. 空值问题

SELECT employee_id
FROM employees;  -- 返回107行记录

SELECT employee_id
FROM employees
WHERE employee_id IN (
					SELECT manager_id
					FROM employees
				); -- 返回18行记录
										
SELECT employee_id
FROM employees
WHERE employee_id NOT IN (
						SELECT manager_id
						FROM employees
						); -- 返回0行记录，因为SELECT manager_id FROM employees中有空值（NULL）
													
SELECT employee_id
FROM employees
WHERE employee_id NOT IN (
						SELECT manager_id
						FROM employees
						WHERE manager_id IS NOT NULL
						); -- 返回89行记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24