MySQL 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入
单行子查询
#4.1 单行操作符： = != > >= < <=
引入
#1. 由一个具体的需求，引入子查询
#需求：谁的工资比Abel的高？
#方式1：
SELECT salary
FROM employees
WHERE last_name = 'Abel';SELECT last_name,salary
FROM employees
WHERE salary > 11000;#方式2：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` #多表的连接条件
AND e1.last_name = 'Abel';#方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');
称谓规范
#2. 称谓的规范：外查询（或主查询）、内查询（或子查询）/*
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 注意事项- 子查询要包含在括号内- 将子查询放在比较条件的右侧- 单行操作符对应单行子查询，多行操作符对应多行子查询*/独立条件子查询 && 行比较
#题目：查询与141号员工的manager_id和department_id相同的其他员工
#的employee_id，manager_id，department_id。
#方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (SELECT manager_idFROM employeesWHERE employee_id = 141)
AND department_id = (SELECT department_idFROM employeesWHERE employee_id = 141)
AND employee_id <> 141;#方式2：了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (SELECT manager_id,department_idFROM employeesWHERE employee_id = 141)
AND employee_id <> 141;CASE 表达式详解
CASE表达式是 SQL 中用于进行条件判断的工具，类似编程语言中的if-else语句。
CASE 被判断的列WHEN 条件值1 THEN 结果1WHEN 条件值2 THEN 结果2...ELSE 默认结果
END 列别名
#题目：显式员工的employee_id,last_name和location。 ( location 需要计算得出)#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’Canada’，其余则为’USA’。SELECT employee_id,last_name,CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'ELSE 'USA' END "location"
FROM employees;子查询空值问题
- 空值产生：表无匹配
last_name = 'Haas'员工时，子查询返回空集，主查询条件等价于job_id = NULL。 - NULL 特性：
NULL代表未知值，用=、!=等比较，结果为 “未知（UNKNOWN）” ，使主查询条件不成立，返回空结果，即便表有job_id为NULL的员工也选不出。 - 解决办法：用
IN替代=处理子查询可能无结果的情况，逻辑更合理，也可结合IS NULL显式处理含NULL的场景 。
多行子查询
多行子查询的操作符：
IN ANY ALL SOME(同ANY)
IN ANY ALL SOME(同ANY)
非法使用子查询
#4.3 非法使用子查询
#错误：Subquery returns more than 1 row
SELECT employee_id, last_name
FROM employees
WHERE salary =(SELECT MIN(salary)FROM employeesGROUP BY department_id); #5.多行子查询
#5.1 多行子查询的操作符： IN ANY ALL SOME(同ANY)#5.2举例：
# IN:
SELECT employee_id, last_name
FROM employees
WHERE salary IN(SELECT MIN(salary)FROM employeesGROUP BY department_id);
# 题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、
# 姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');
#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、
#姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');
重点题目
#题目：查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sal)FROM(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal 子查询,表要起别名);#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL( SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) 子查询存在NULL值
子查询含 NULL + 主查询用 IN：
仅匹配子查询中的非 NULL 值，NULL 会被忽略，结果正常（只返回主查询字段在子查询非 NULL 集合中的记录）。子查询含 NULL + 主查询用 NOT IN：
结果必为空集（因x != NULL结果为 UNKNOWN，导致整体条件失效）。NOT IN等价于 “值不等于子查询中的所有值”（即x != a AND x != b AND ...）。- 若子查询含
NULL，则条件中会包含x != NULL，而其结果为UNKNOWN。 - 由于
AND逻辑中只要有一个UNKNOWN，整体结果就为UNKNOWN（MySQL 视为FALSE），因此主查询返回空集。
建议：子查询可能有 NULL 时，避免用 NOT IN，改用 NOT EXISTS。