MYSQL-子查询

单行子查询

单行操作符：= != > >= < <=

• 普通单行子查询

#查询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
);

• 包含聚合函数的单行子查询

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

SELECT
	department_id,
	MIN(salary)
FROM
	employees
WHERE
	department_id IS NOT NULL
GROUP BY
	department_id
HAVING
	MIN(salary) > (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			department_id = 110
	);

• 包含流程控制函数的单行子查询

-- 查询员工的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;

• 空值问题

#已知manager_id有为null的数据
SELECT
	last_name
FROM
	employees
WHERE
	employee_id NOT IN (
		SELECT
			manager_id
		FROM
			employees
	);
-- 返回结果为空
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');
-- 返回结果为空

• 使用子查询时，首先需确定子查询中是否含有NULL值

多行子查询

IN

#查询公司管理者的employee_id，last_name，job_id，department_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
	);

ANY

• SELECT …FROM… WHERE expr > ANY(…)： 即expr大于子查询中的任意一个

-- 查询其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、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'
);

ALL

• SELECT …FROM… WHERE expr > ALL(…)： 即expr大于子查询中的每一个

# 查询工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT
	last_name,
	job_id,
	salary
FROM
	employees
WHERE
	salary > ALL (
		SELECT
			salary
		FROM
			employees
		WHERE
			job_id = 'SA_MAN'
	);

SOME

• 同ANY

EXISTS

• 检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS执行顺序

1. 首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
2. 遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id
3. 如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行；否则EXISTS子句返回FALSE，且不能作为结果。

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT
	e.employee_id,
	e.last_name,
	e.job_id,
	e.department_id
FROM
	employees e
WHERE
	EXISTS (
		SELECT
			manager_id
		FROM
			employees
		WHERE
			manager_id = e.employee_id
	);

IN与EXISTS的区别

1. 查询效率：IN的查询效率高于EXISTS；
2. 运行方式：

• EXISTS：先运行主查询一次，再去子查询里查询对应结果是否存在，如果存在，返回ture，反之返回false。再根据主查询中的每一行去子查询里去查询。
• IN：子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表，符合要求的输出，反之则不输出。

NOT EXISTS

• 查询结果与EXISTS相反

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

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

COUNT(*)子查询

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

不同查询语句查询同一结果

• 查询平均工资最低的部门信息

方式一

#查询平均工资最低的部门信息

SELECT
	*
FROM
	departments
WHERE
	department_id = (
		SELECT
			department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING
			AVG(salary) = (
				SELECT
					MIN(avg_salary) AS min_salary
				FROM
					(
						SELECT
							AVG(salary) avg_salary
						FROM
							employees
						WHERE
							department_id IS NOT NULL
						GROUP BY
							department_id
					) department_avg
			)
	);

方式二：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
				WHERE
					department_id IS NOT NULL
				GROUP BY
					department_id
			)
	);

方式三：LIMIT

#方式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
				WHERE
					department_id IS NOT NULL
				GROUP BY
					department_id
				ORDER BY
					AVG(salary) ASC
				LIMIT 0,1
			)
	);

方式四：子查询作为FROM条件

SELECT
	*
FROM
	departments d,
	(
		SELECT
			department_id,
			AVG(salary)
		FROM
			employees
		WHERE
			department_id IS NOT NULL
		GROUP BY
			department_id
		ORDER BY
			AVG(salary) ASC
		LIMIT 1
	) s
WHERE
	d.department_id = s.department_id;