子查询

子查询

为什么引入单行子查询
查询职位与SMITH相同的员工
SELECT job
FROM emp
WHERE ename = 'SMITH' --得到CLERK
SELECT empno, ename, sal, job
FROM emp
WHERE job = 'CLERK'
得到结果有点繁琐，如果镶嵌到软件中，有点恶心。

WHERE子句中的单行子查询
可以将上面的两个查询语句集成在一个主查询（Main Query）和一个子查询（Subquery）中，如：
SELECT empno, ename, sal, job
FROM emp
WHERE job =
(SELECT job
FROM emp
WHERE ename = 'SMITH');
括号内的查询叫做子查询（Subquery）或内查询（Inner query），括号外的查询叫主查询（Main query）或外查询（Outer query）
Oracle执行查询语句的顺序如下：
Oracle首先执行括号内的子查询
再将子查询出的结果放在主查询中运行
单行子查询的比较运算符：
>
>=
<
<=
=
<>或!=

单行子查询除了可以放在WHERE子句中，还可以放在HAVING子句和FROM子句中。
书写单行子查询使用单行比较关系符
单行子查询放在单行比较关系符的右边
单行子查询放在括号中
单行子查询不能使用ORDER BY子句

根据上面的要求，单行子查询必须返回单一的行。稳妥的办法是在子查询的条件中使用主键（Primary key）

HAVING子句中的单行子查询
SELECT job， MIN(sal), AVG(sal), MAX(sal)
FROM emp
WHERE job NOT LIKE 'PRESID%'
GROUP BY job
HAVING AVG(sal) > (
SELECT MIN(AVG(sal))
FROM emp
GROUP BY job);

FROM子句中的单行子查询
SELECT e.empno, e.ename, e.sal, e.job, a.avesal
FROM emp e, (SELECT job, AVG(sal) avesal
FROM emp
GROUP BY job)a
WHERE e.job = a.job
AND e.sal > a.avesal
AND e.job != 'CLERK';

多行子查询
前面介绍了单行子查询。现在介绍另一类子查询，即多行子查询，多行子查询使用多行比较操作符。它返回多行。
多行比较操作符包括：
IN
ANY
ALL

使用IN操作符的多行子查询
SELECT empno, ename, job, sal
FROM emp
WHERE sal IN (SELECT MAX(sal)
FROM emp
GROUP BY job)
AND job <> 'CLERK'
AND job NOT LIKE 'PRES%';
IN的含义为子查询返回列表中的任何一个。IN操作符比较子查询返回列表中的每一个值，并且显示任何相等的数据行

使用ALL操作符的多行子查询
如果想要知道哪些员工比任何职位的平均工资还要低
SELECT empno, ename, sal, job
FROM emp
WHERE sal < ALL
(SELECT AVG(sal)
FROM emp
GROUP BY job);
ALL操作符比较子查询返回列表中的每一个值。<ALL为小于最小的；>ALL为大于最大的，=ALL会返回未选定行

使用ANY操作符的多行子查询
SELECT empno， ename, sal, job
FROM emp
WHERE sal > ANY
(SELECT AVG(sal)
FROM emp
GROUP BY job);
ANY操作符比较子查询返回列表中的每一个值。<ANY为小于最大的，>ANY为大于最小的，=ANY相当于IN

子查询中的空值（NULL）问题
子查询的得到的结果为NULL会影响主查询，得到的结果为为选定行
SELECT e.empno, e.ename, e.sal, e.job
FROM emp e
WHERE e.mgr NOT IN
(SELECT w.mgr
FROM emp w
WHERE w.mgr IS NULL)
要注意NULL的感染性

多列子查询
成对比较的多列子查询
SELECT empno,ename,sal,job
FROM manager
WHERE (sal,job) IN
(SELECT MAX(sal),job
FROM manager
GROUP BY job);
成对匹配的话只有两者完全匹配才可以

非成对比较的多列子查询
SELECT empno, ename, sal, job
FROM manager
WHERE sal IN (SELECT MAX(sal)
FROM manager
GROUP BY job)
AND job IN (SELECT DISTINCT job
FROM manager);
不成对的话使用AND连接多个子查询列。