相关子查询

关联子查询

关联子查询，是指子查询的执行依赖于外部查询，通常是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次。

1. 查询员工工资大于本部门平均工资的员工的last_name、salary和department_id。

# 查询员工中工资大于本部门平均工资的员工的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
				); -- 返回38行记录
								
# 方式2
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
				SELECT department_id,AVG(salary) avg_sal
				FROM employees 
				GROUP BY department_id
				) t_dep_avg_sal
WHERE e.department_id = t_dep_avg_sal.department_id
AND e.salary > t_dep_avg_sal.avg_sal; -- 返回38行记录

1. 查询员工的employee_id、salary，按照department_name排序。

# 查询员工的employee_id、salary，按照department_name排序
SELECT department_id,salary
FROM employees e
ORDER BY (
			SELECT department_name
			FROM departments d
			WHERE e.department_id = d.department_id
			); -- 返回107行记录

1. 如果employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id、last_name和job_id。

# 如果employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id、last_name和job_id。
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<= (
			SELECT COUNT(*)
			FROM job_history j
			WHERE e.employee_id = j.employee_id
			) -- 返回3行记录

关键字：EXISTS 和 NOT EXISTS

关联子查询，通常会和EXISTS一起使用，用来检查子查询中是否有满足条件的行。

• 如果子查询中不存在满足条件的行，EXISTS会返回false，并继续在子查询中查找。
• 如果子查询中存在满足条件的行，则停止查找，返回true。

1. 查询公司管理者的employee_id、last_name、job_id、department_id。

# 查询公司管理者的employee_id、last_name、job_id、department_id
# 方式1：自连接
SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1,employees e2
WHERE e1.employee_id = e2.manager_id;

# 方式2：子查询（多行子查询）
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
					SELECT DISTINCT manager_id 
					FROM employees
					)
										
# 方式3：子查询（关联子查询）
SELECT employee_id,last_name,job_id,department_id
FROM employees e1 
WHERE EXISTS (
			SELECT *
			FROM employees e2
			WHERE e1.employee_id = e2.manager_id
			)

1. 查询departments表中，不存在于employees表中的部门的department_id和department_name。

# 查询departments表中，不存在于employees表中的部门的department_id和department_name
# 方式1；右外连接
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 返回16行记录

# 方式2：关联子查询（NOT EXISTS）
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
				SELECT DISTINCT department_id
				FROM employees e
				WHERE d.department_id = e.department_id
				); -- 返回16行记录

相关链接

MySQL的子查询