MySQL子查询

子查询（嵌套查询）

子查询是指一个查询语句嵌套在另一个查询语句的内部的查询

eg:要查询员工工资比‘Abel’高的员工名字
SELECT name,salary     #外查询(主查询)
FROM employees
WHERE salary > (#查询'Abel'的工资
                SELECT salary       #内查询(子查询)
                FROM employees
                WHERE name = 'Abel'
                );

子查询在主查询之前执行
子查询的结果供外查询使用
子查询在（）内
单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

角度一（内查询返回结果的条目）：

角度二（内查询是否执行多次）：

相关子查询：内查询的结果与外查询的条件有关
不相管子查询：内查询的结果与外查询的条件无关

eg:查询工资大于本部门的员工：相关

查询工资大于公司平均工资的员工：不相关

单行子查询

单行比较操作符

=、<、<=、>、>=、<>

HARING中的子查询

先执行子查询
向HARING中返回结果

eg:要查询最低工资大于50号部门最低工资的部门id
SELECT department_id 
FROM employees
GROUP BY department_id
HARING min(salary) > (
                     SELECT min(salary)       
                     FROM employees
                     WHERE deppartment_id = 50
                    );

CASE中的子查询

eg:查询员工的id，其部门id与地址id是1800的员工相同的是'China'，不同的是'usa'
SELECT employees_id,(CASE department_id WHEN (SELECT department_id
                                              FROM departments
                                              WHERE location_id = 1800) RHEN 'china'  
                                        else 'usa' END ) "location"（别名）

FROM employees;

子查询的空值问题

内查询结果是空或是没有，不会报错，但是不会显示结果

非法使用子查询

单行的操作符对应多行的子查询等情况

多行子查询

也叫做集合比较子查询
内查询返回多行
使用多行操作符

多行比较操作符

SELECT name,salary     
FROM employees
WHERE salary IN (#各部门的最低工资
                SELECT MIN(salary)       
                FROM employees
                GROUP BY department_id
                );

ANY \ ALL

eg:返回其他job_id中比job_id为'IT'部门的 *任一* 工资低的员工的员工id
SELECT id
FROM employees
WHERE job_id <> 'IT'
and salary < ANY (SELECT salary
                  FROM employees
                  WHERE job_id ='IT'
                 );

eg:返回其他job_id中比job_id为'IT'部门的 *所有* 工资低的员工的员工id
SELECT id
FROM employees
WHERE job_id <> 'IT'
and salary < ALL (SELECT salary
                  FROM employees
                  WHERE job_id ='IT'
                 );

查平均工资最低的部门id
方式一：在FROM中使用子查询
SELECT department_id
FROM employees
GROUP BY department_id
HARING AVG(salary) = (SELECT MIN(avg_sal)
                      FROM(SELECT AVG(salary) avg_sal #把查出的各部门的平均工资当成一个新表来使用 
                           FROM employees
                           GROUP BY department_id
                          ) "t_dept_avg_sal"
);

方式二：使用ALL
SELECT department_id
FROM employees
GROUP BY department_id
HARING AVG(salary) <= ALL (SELECT AVG(salary) 
    /*在内查询的结果中*/    FROM employees
                           GROUP BY department_id
                          );

空值问题

内查询结果有null时，外查询不会出结果

相关子查询

EXISTS和 NOT EXISTS关键字

用来检查在子查询中是否存在满足条件的行

类似于C语言中的break

EXISTS

不存在满足条件的行

条件返回FALSS

继续在子查询中寻找

存在满足条件的行

不在子查询中继续寻找

条件返回TRUE

查询公司管理者的ID
方式1：自连接
SELECT DISTINCT t2.employee_id   #去重
FROM employees t1 JOIN employees t2
ON t1.manger_id = t2.employee_id;

方式2：子查询
SELECT employee_id
FROM employees 
WHERE employee_id IN (SELECT DISTINCT manger_id
                       FROM employees                                          );

方式3：使用EXISTS
SELECT t1.employee_id
FROM employees t1
WHERE EXISTS  (SELECT *
               FROM employees t2
               WHERE t1.employee_id = t2.manger_id
              );

NOT EXISTS

NOT EXISTS表示如果不存在某种条件，则返回TRUE，否则返回FALSS

查询部门表中不存在于员工表的部门ID
方式一：外连接
SELECT t2.department_id
FROM employees t1 RIGHT JOIN departments t2
ON t1.department_name = t2.department_name
WHERE t1.department is null;

方式二：
SELECT department_id
FROM departments t1
WHERE NOT EXISTS(SELECT *
                 FROM employees t2
                 WHERE t1.department_name = t2.department_name
               );