第九课 子查询

• 含义：出现在其他语句中的SELECT语句，称为子查询或内查询。外部的查询语句称为主查询或外查询
• 分类：
  • 按子查询出现的位置：
    • SELECT语句后边：仅仅支持标量子查询
    • FROM语句后边：支持表子查询
    • WHERE或HAVING语句后边：支持标量子查询、列子查询、行子查询
    • EXISTS语句后边（也被称为相关子查询）：表子查询
  • 按结果集的行列数不同：
    • 标量子查询（结果集只有一行一列）
    • 列子查询（结果集只有一列多行）
    • 行子查询（结果集有一行多列）
    • 表子查询（结果集一般为多行多列）

1 WHERE 或 HAVING 语句后边

• 特点：
  • 子查询放在小括号内
  • 子查询一般放在条件的右侧
  • 标量子查询，一般搭配单行操作符使用（> < >= <= <>）
  • 列子查询，一般搭配多行操作符使用（IN，ANY/SOME，ALL）
  • 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

操作符	含义
IN或NOT IN	等于列表中的任意一个
ANY或SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

1.1 标量子查询

• 不要非法使用标量子查询（例如：查出的结果不是一行一列，或子查询的查询结果为空）

# WHERE 或 HAVING 语句后边 --> 标量子查询
# 1 谁的工资比Abel高
SELECT
	emp.last_name,
	emp.salary
FROM
	employees emp
WHERE
	emp.salary > (
		SELECT
			salary
		FROM
			employees
		WHERE
			employees.last_name = 'Abel'
	);

# 2 返回JOB_ID与141号员工相同，SALARY比143号员工多的员工，姓名，JOB_ID和工资
SELECT
	emp.last_name,
	emp.job_id,
	emp.salary
FROM
	employees emp
WHERE
	emp.job_id = (
		SELECT
			job_id
		FROM
			employees
		WHERE
			employees.employee_id = '141'
	)
AND emp.salary > (
	SELECT
		salary
	FROM
		employees
	WHERE
		employees.employee_id = '143'
);

# 3 返回公司工资最少的员工LAST_NAME,JOB_ID和SALARY
SELECT
	emp.last_name,
	emp.job_id,
	emp.salary
FROM
	employees emp
WHERE
	emp.salary = (
		SELECT
			MIN(salary)
		FROM
			employees
	);

# 4 查询最低工资大于50号部门最低工资的部门ID和其最低工资
SELECT
	MIN(emp.salary),
	emp.department_id
FROM
	employees emp
GROUP BY
	emp.department_id
HAVING
	MIN(emp.salary) > (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			department_id = '50'
	);

1.2 列子查询

# WHERE 或 HAVING 语句后边 --> 列子查询
# 1 返回LOCATION_ID是1400或1700的部门中的所有员工姓名
SELECT
	emp.last_name,
	emp.department_id
FROM
	employees emp
WHERE
	emp.department_id IN (
		SELECT
			dep.department_id
		FROM
			departments dep
		WHERE
			dep.location_id IN ('1400', '1700')
	);

# 2 返回其它工种中比JOB_ID为‘IT_PROG’工种任一工资低的员工的员工号、姓名、JOB_ID以及SALARY
SELECT
	emp.employee_id,
	emp.last_name,
	emp.job_id,
	emp.salary
FROM
	employees emp
WHERE
	emp.salary < ANY (
		SELECT
			salary
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

# 3 返回其他工种比JOB_ID为‘IT_PROG’部门所有工资都低的员工工号、姓名、JOB_ID以及SALARY
SELECT
	emp.employee_id,
	emp.last_name,
	emp.job_id,
	emp.salary
FROM
	employees emp
WHERE
	emp.salary < ALL (
		SELECT
			salary
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

1.3 行子查询

# WHERE 或 HAVING 语句后边 --> 行子查询
# 1 查询员工编号最小并且工资最高的员工信息
# 一般写法
SELECT
	*
FROM
	employees emp
WHERE
	emp.salary = (
		SELECT
			MAX(emp.salary)
		FROM
			employees emp
	)
AND emp.employee_id = (
	SELECT
		MIN(emp.employee_id)
	FROM
		employees emp
);

# 行子查询写法
SELECT
	*
FROM
	employees emp
WHERE
	(emp.employee_id, emp.salary) = (
		SELECT
			MIN(employee_id),
			MAX(salary)
		FROM
			employees
	);

2 SELECT 语句后边

• 只支持标量子查询（查询结果为一行一列）

# SELECT 语句后边
# 1 查询每个部门的员工个数
SELECT
	dep.*, (
		SELECT
			COUNT(*)
		FROM
			employees emp
		WHERE
			emp.department_id = dep.department_id
	)
FROM
	departments dep;

# 2 查询员工号 = 102 的部门名
SELECT
	(
		SELECT
			dep.department_name
		FROM
			departments dep
		INNER JOIN employees emp ON dep.department_id = emp.department_id
		WHERE
			emp.employee_id = '102'
	);

3 FROM 语句后边

• 将子查询结果充当一张表，要求必须起别名

# FROM 语句后边
# 1 查询每个部门的平均工资的工资等级
SELECT
	A.departID,
	jobg.grade_level,
	A.avgSalary
FROM
	(
		SELECT
			emp.department_id AS departID,
			AVG(emp.salary) AS avgSalary
		FROM
			employees emp
		GROUP BY
			emp.department_id
	) A
INNER JOIN job_grades jobg ON A.avgSalary BETWEEN jobg.lowest_sal
AND jobg.highest_sal;

4 EXISTS 语句后边

• 语法：

EXISTS(完整的查询语句)
# 返回结果只有可能是1（存在）/ 0（不存在）

# EXISTS 语句后边
# 1 查询有员工的部门名
SELECT
	dep.department_name
FROM
	departments dep
WHERE
	EXISTS (
		SELECT
			*
		FROM
			employees emp
		WHERE
			dep.department_id = emp.department_id
	);