MYSQL关联子查询与 EXISTS关键字

1 相关子查询执行流程

2 SQL示例

-- 相关子查询，查找工资大于同部门平均工资的员工信息
SELECT
	last_name,
	salary,
	department_id 
FROM
	employees e1
WHERE
	e1.salary > (
	SELECT
		avg( salary ) 
	FROM
		employees e2
WHERE
	e2.department_id = e1.department_id)

3 EXISTS与NOT EXISTS关键字

3.1 EXISTS的使用

-- 求管理者的 employee_id，last_name，job_id，department_id，   manager_id是管理者的employee_id。
-- 方式一
SELECT DISTINCT
	e2.employee_id,
	e2.last_name,
	e2.job_id,
	e2.department_id 
FROM
	employees e1
	JOIN employees e2 ON e1.manager_id = e2.employee_id;.
	

-- 方式二	
SELECT
	employee_id,
	last_name,
	job_id,
	department_id 
FROM
	employees 
WHERE
	employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL );
	
-- 方式三：用EXISTS，用外层的SELECT去对比内层的SELECT，内层WHERE为TRUE就说明存在，则显示出来。
SELECT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id 
FROM
	employees e1 
WHERE
	EXISTS (
	SELECT
		* 
	FROM
		employees e2 
	WHERE
	e1.employee_id = e2.manager_id);

3.2 NOT EXISTS的使用

-- 题目：查询departments表中，不存在于employees表中的部门的department_id 与 department_name。
SELECT
	department_id,
	department_name 
FROM
	departments d 
WHERE
	NOT EXISTS (
	SELECT
		* 
	FROM
		employees e 
WHERE
	d.department_id = e.department_id)