子查询

一、问题引入

需求：查询出谁的工资比Abel的高？(使用自连接比子查询效率高)

#方式1：分两步
#查询出Abel的工资
SELECT salary,last_name
FROM employees
WHERE last_name = 'Abel';#11000

SELECT last_name,salary
FROM employees
WHERE salary > 11000;#10条数据

#方式2：自连接
SELECT e1.last_name,e1.salary
FROM employees e1 JOIN employees e2 
ON e1.salary > e2.salary
WHERE e2.last_name = 'Abel';

#方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary >  (
								SELECT salary 
								FROM employees
								WHERE last_name = 'Abel'
								);

二、子查询概念及注意事项

称谓：外查询（主查询）、内查询（子查询）

子查询在主查询之前一次执行完成；

子查询的结果被主查询使用；

子查询要包含在括号内；

子查询放在比较条件的右侧；

单行操作符对应单行子查询，多行操作符对应多行子查询。

三、分类

1.内查询返回的结果条目数

单行子查询 VS 多行子查询

2.内查询是否被执行多次

四、多行子查询（集合比较子查询）

特点：1.内查询返回多行；2.需要使用多行比较操作符

1.多行比较操作符

IN：等于列表中任意一个

ANY：需要和单行比较操作符（=、>、<等）一起使用，和子查询返回的某一个值比较（存在）

ALL：需要和单行比较操作符（=、>、<等）一起使用，和子查询返回的所有值比较（任意）

SOME：ANY的别名，同ANY，常用ANY

结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询。

五、EXISTS与NOT EXISTS(见例7、8)

六、例题

#1查询最低工资大于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
											GROUP BY department_id
											);
											
#2显示员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同
#则location为'Canada'，否则为'USA'
SELECT employee_id,last_name, department_id,CASE department_id WHEN (
                                                                                                                                SELECT department_id
                                                                                                                                FROM departments
                                                                                                                                WHERE location_id = 1800
                                                                                                                                ) THEN 'Canada' ELSE 'USA' END "location"
FROM employees;

#3.返回其他job_id中比job_id为'IT_PROG'部门任一员工工资低的员工的信息
SELECT employee_id,last_name,salary,job_id
FROM employees
WHERE job_id<>'IT_PROG'
AND salary < ANY(
									SELECT salary
									FROM employees
									WHERE job_id = 'IT_PROG'
									);
#4查询平均工资最低的部门id
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
									SELECT AVG(salary)
									FROM employees
									GROUP BY department_id
									);
#方式2：
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
						  )  t_dep_minsal
					);
					
#5查询员工中工资大于本部门平均工资的员工的last_name,salary,department_id
#方式1：相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
							SELECT AVG(salary)
							FROM employees e2 
							WHERE e1.department_id = e2.department_id
							GROUP BY department_id
							);
#方式2：在FROM中声明子查询，造一张表，两列（部门和该部门平均工资）
SELECT last_name,salary,e1.department_id
FROM employees e1 ,(
										SELECT department_id,AVG(salary) avg_dep
										FROM employees
										GROUP BY department_id
										) t_dep_avg_dep
WHERE e1.department_id = t_dep_avg_dep.department_id
AND e1.salary > t_dep_avg_dep.avg_dep;

#6查询员工的salary,department_id，并按department_name排序
SELECT salary,department_id
FROM employees e 
ORDER BY (
					SELECT department_name
					FROM departments d 
					WHERE e.department_id = d.department_id
					); 

#7查询公司管理者的last_name,employee_id，job_id,department_id信息
#方式1：自连接
SELECT DISTINCT mgr.last_name,mgr.employee_id,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr 
ON emp.manager_id = mgr.employee_id;

#方式2：子查询
SELECT DISTINCT mgr.last_name,mgr.employee_id,mgr.job_id,mgr.department_id
FROM employees mgr 
WHERE mgr.employee_id IN (
													SELECT manager_id
													FROM employees emp 
													);

#方式3：使用EXISTS
SELECT DISTINCT mgr.last_name,mgr.employee_id,mgr.job_id,mgr.department_id
FROM employees mgr 
WHERE EXISTS(
						SELECT *
						FROM employees emp
						WHERE mgr.employee_id = emp.manager_id
						);
						
#8.查询哪些部门没有员工
SELECT department_id,department_name
FROM departments dep
WHERE NOT EXISTS(
									SELECT *
									FROM employees emp
									WHERE dep.department_id = emp.department_id
									);
#9查询和姓名中包含u的员工在相同部门的员工的last_name,employee_id
SELECT last_name,employee_id
FROM employees
WHERE department_id IN (
												SELECT  department_id
												FROM employees
												WHERE last_name LIKE '%u%'
												);
#10查询平均工资最低的部门信息
#方式1：先找各部门平均工资，再找出最低的，再找出部门号
SELECT * 
FROM departments
WHERE department_id = (
						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
												) t_dep_avgsal
											)
					);
#方式2：ALL
SELECT * 
FROM departments
WHERE department_id = (
												SELECT department_id
												FROM employees	
												GROUP BY department_id
												HAVING avg(salary) <= ALL (
																									SELECT AVG(salary)
																									FROM employees
																									GROUP BY department_id
																									)
												);
#方式3：LIMIT
SELECT * 
FROM departments
WHERE department_id = (
												SELECT department_id
												FROM employees	
												GROUP BY department_id
												HAVING avg(salary) =     (
																									SELECT AVG(salary) 
																									FROM employees
																									GROUP BY department_id
																									ORDER BY AVG(salary)
																									LIMIT 0,1
																									)
												);
#方式4：
SELECT d.*
FROM departments d,(
										SELECT department_id,AVG(salary) avg_sal 
										FROM employees
										GROUP BY department_id
										ORDER BY AVG(salary)
										LIMIT 0,1
										) t_dep_avgsal
WHERE d.department_id = t_dep_avgsal.department_id;

#11查询每个部门下，部门人数大于5的部门名称
SELECT department_name
FROM departments d 
WHERE 5 < (
					SELECT COUNT(*)
					FROM employees e
					WHERE e.department_id = d.department_id
					);

结论：①子查询叫简单，一般从外往里编写；