MySQL数据库，子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

例：想查询工资比BLACK的工资高的员工的信息

SELECT ename , sal 
FROM emp
WHERE sal > (
			SELECT sal 
			FROM emp 
			WHERE ename = 'BLAKE'
            );

外部的查询称为外查询（或主查询），内部的查询称为内查询（或子查询）。

注：

• 子查询在主查询之前执行完成。

• 子查询的结果被主查询使用。

• 子查询要包含在括号内。

• 将子查询放在比较条件的右侧（建议）。

• 单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询的分类：

角度一：单行子查询、多行子查询

单行子查询返回一条记录，多行子查询返回多条记录。

角度二：相关子查询、不相关子查询（或关联子查询和不关联子查询）（即从内查询是否执行多次的角度）

•  子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件执行，那么这样的子查询称为不相关子查询。

• 如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

单行子查询：

单行比较操作符：

注：

• 子查询可以放在任何可以用“值”表示的位置。比如WHERE、HAVING里的表达式中，SELECT中的字段中，CASE表达式中等等。

• 子查询只返回一个结果值，不会返回行。

• 子查询查询不到结果则返回NULL。

• 多行子查询的结果不能放在单行比较操作符后使用。

例，在CASE表达式中使用单行子查询：

SELECT empno , ename , CASE deptno
WHEN (
      SELECT deptno
      FROM dept
      WHERE loc = 'NEW YORK'
     ) 
THEN 'AAA'
ELSE 'BBB'
END 'location' #别名
FROM emp;

多行子查询：

多行子查询也称为集合比较子查询，返回多行，使用多行比较操作符。

多行比较操作符：

注：

可以将多行子查询的结果当作一张表使用，但是，必须要给此表取别名。

例如：

SELECT MIN(sal_avg)
FROM (
      SELECT AVG(sal) sal_avg
      FROM emp
      GROUP BY deptno
     ) das; -- das即为此表的别名。

多行子查询中的空值问题：

如果多行子查询的结果的其中一条中出现NULL，用NOT IN加此子查询的结果为空，因为NOT IN实际上会用<>来与每个结果比较，若结果中有空值那么 <>NULL的结果就是NULL，正确的做法应该是将多行子查询的结果中的NULL值过滤掉。

例：

SELECT ename
FROM emp
WHERE NOT IN (
              SELECT mgr
              FROM emp
              WHERE mgr IS NOT NULL
             );

相关子查询：

如果相关子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重写计算一次，这样的子查询就是关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

例，查询员工中工资大于本部门平均工资的员工的姓名、薪资、和部门号（使用相关子查询）：

SELECT ename , sal , deptno
FROM emp e1
WHERE sal > (
             SELECT AVG(sal)
             FROM emp e2
             WHERE deptno = e1.deptno   #子查询要与主查询做相关联，即主查询中的每一行的执行都要重新执行一次子查询。
            );

在SELECT中除了GROUP BY和LIMIT之外，其他的位置都可以使用子查询。

EXISTS与NOT EXISTS

关联子查询通常也会和EXISTS操作符一起来使用，用来检查子查询中是否存在行。

每一行执行时：

如果在子查询的结果中不存在行，返回FALSE。

如果在子查询的结果中存在行，返回TRUE。

返回FALSE即主查询中的相应的行不会成为主查询的结果，返回TRUE即主查询中的相应的行会成为主查询的结果。

NOT EXISTS表示如果在子查询的结果中不存在行，则返回TRUE，否则返回FALSE（与EXISTS相反）。

EXISTS后的子查询并不会返回行，只会返回TRUE或FALSE。

例：

SELECT empno , ename , job , deptno
FROM emp e1
WHERE EXISTS (
              SELECT *
              FROM emp e2
              WHERE e1.empno = e2.mgr
              );

当员工是属于管理者时，主查询才会接收到子查询返回的TRUE。

相关子查询也可以使用到更新、删除操作中，称为相关更新、相关删除。

多层自查询的三种经典的查询方式：

例，查询公司中平均工资最高的工作职位：

方式一：

步骤：

①    先求各部门的平均工资

②    再到①中的数据中求出最高的平均工资

③    再根据②中的数据对应出最高的平均工资对应的工作职位

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) = (
                   SELECT MAX(avg_sal)
                   FROM (
                         SELECT AVG(sal) avg_sal
                         FROM emp
                         GROUP BY job
                         ) avg_tab
                  );

方式二：

比起方式一，方式二直接 <= ALL的方式将求最大的平均工资的步骤隐藏在比较操作符中，减少了子查询的次数。

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) >= ALL (
                        SELECT AVG(sal)
                        FROM emp
                        GROUP BY job    
                        );

方式三：

方式三使用的是先降序排列，再用分页LIMIT的方式，只取第一个记录，取得的即是最高的平均工资，也减少了子查询的次数。

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) = (
                   SELECT AVG(sal) avg_sal
                   FROM emp
                   GROUP BY job
                   ORDER BY avg_sal DESC
                   LIMIT 0,1
                  );