DQL语言7：子查询

含义

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句称为主查询或外查询

子查询的分类

按结果集的行列数不同：

标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果一般为多行多列）

按子查询出现的位置：

select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面（*）：支持标量子查询（*）、列子查询（*）、行子查询
exists后面（相关子查询）：支持表子查询

where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询★

-- 案例1：谁的工资比 Abel 高?

#①查询Abel的工资（结果是一行一列）
SELECT salary FROM employees
WHERE last_name = 'Abel';
#②查询员工的信息，满足 salary>①结果
SELECT * FROM employees
WHERE salary > (
	SELECT salary FROM employees
	WHERE last_name = 'Abel'
);

-- 案例2：返回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
);


-- 案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary) FROM employees
);

-- 案例4：查询最低工资大于 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
);


-- 非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary  #行数不为1
	FROM employees
	WHERE department_id = 50
);

2.列子查询（多行子查询）★

ALL , ANY|SOME, IN|NOT IN

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号
SELECT department_id FROM departments
WHERE location_id IN (1400,1700);

#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name FROM employees
WHERE department_id IN(
	SELECT department_id FROM departments
	WHERE location_id IN (1400,1700)
);


#案例2：返回其它工种中比job_id为‘IT_PROG’工种的任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE 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 employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT salary FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

3.行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息

#①查询最小的员工编号
SELECT MIN(employee_id) FROM employees;

#②查询最高工资
SELECT MAX(salary) FROM employees;

#③查询员工信息
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id) FROM employees
)
AND salary = (
	SELECT MAX(salary) FROM employees
);

#合起来写：
SELECT *
FROM employees
WHERE (employee_id, salary)=(
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

select后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT *, (
	SELECT COUNT(*) 
	FROM employees e
	WHERE e.department_id = d.`department_id`
)个数 
FROM departments d;
 
 
#案例2：查询员工号=102的部门名
SELECT (
	SELECT department_name 
	FROM employees e
	INNER JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE e.`employee_id` = 102
) 部门名;

from后面

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

#案例：查询每个部门的平均工资的工资等级

#①查询每个部门的平均工资
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT department_id, avg_salary, grade_level
FROM (
	SELECT department_id, AVG(salary) avg_salary
	FROM employees
	WHERE department_id IS NOT NULL
	GROUP BY department_id
) ad
INNER JOIN job_grades jg
ON ad.avg_salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

exists后面（相关子查询）

将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果
往往可以用in替代

语法：

exists(完整的查询语句)

结果：返回BOOL值（1或0）

#案例：查询没有女朋友的男神信息
#in
SELECT b.*
FROM boys b
WHERE b.`id` NOT IN (
	SELECT boyfriend_id
	FROM beauty
);

#exists
SELECT b.*
FROM boys b
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty
	WHERE b.`id` = beauty.`boyfriend_id`
);