一、子查询概述
定义：
- 出现在其他语句中的select语句，称为子查询或内查询
- 外部的查询语句，称为主查询或外查询
为什么要学习子查询？
例：查询工资比张三高的员工信息
-- 查询工资比张三高的员工信息 -- 1.查询张三的工资 select sal from emp where ename='张三'; -- 2. 比3000元高的员工信息 select * from emp where sal >3000;
张三的工资是3000元，这3000元是作为条件，在第二步当作为条件使用。
这里需要两个步骤，而使用子查询之后，使用一步就可以解决该问题。
因此子查询要解决的问题是：问题不能一步求解的问题。
二、子查询的使用
子查询的语法：select语句的嵌套。
SELECT select_list FROM table WHERE expr operator ( SELECT select_list FROM table )
使用子查询解决：查询工资比张三高的员工信息
select * from emp where sal > ( select sal from emp where ename='张三' );
子查询注意的10个问题：
【1】子查询语法中的小括号
select e.* from sal > (select min(sal) from emp where deptno=30)
【2】子查询的书写风格
【3】可以使用子查询的位置：where，select，having，from
【4】不可以使用子查询的位置：group by
【5】强调：from 后面的子查询语句
【6】子查询和主查询可以不是同一张表
只要子查询的结果在主查询当中可以使用就行了。
例子：查询部门名称是行政部的员工信息
--方法一：使用子查询 -- 这里子查询是使用表dept，而外层则是使用emp表 select * from emp where deptno=(select deptno from dept where dname='行政部')
--方法二：使用多表查询 -- 从理论上面来讲，使用多表查询好于子查询 select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='行政部'
【7】一般不在子查询中，使用排序（因为没有意义）；
但是在Top-N分析问题中，必须对子查询排序
Top-N分析问题：指的是按照某个规律排序以后，取出前几条记录。
【8】一般先执行子查询，再执行主查询，但相关子查询例外
【9】单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
【10】注意子查询中是null值问题：子查询分为单行子查询和多行子查询
1、单行子查询
- 单行子查询：子查询只返回一条记录
单行操作符：= , > , >= ,< <= ，<>
单行子查询事例：
select * from emp where job=(select job from emp where empno=7655) and sal>(select sal from emp where empno=6088);
-- 查询工资最低的员工的信息 select * from emp where sal=(select min(sal) from emp) -- 在子查询当中也可以使用分组函数
-- 查询最低工资大于20号部门最低工资的部门号和部门的最低工资： --先得到每个部门的最低工资，在查出20号部门的最低工资，然后筛选 select deptno ,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20)
2、多行子查询
- 多行子查询：子查询返回多条记录
多行操作符：
【1】in
【1】IN：等于列表中的任何一个
例子： 查询部门名称是销售部或者财务部的员工信息
方法一：子查询+in
-- 方法一：子查询+in select * from emp where deptno in (select deptno from dept where dname='销售部' or dname='财务部')
方法二：使用多表查询
select e.* -- 查询员工的所有信息 from emp e,dept d where e.deptno=d.deptno and (d.dname='销售部' or d.dname='财务部')
【2】any
【2】ANY：和子查询返回的任意一个值比较
ANY在使用中有如下三种使用形式：
=ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）
>ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）<ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）
例子：查询工资比30号部门任意一个员工高的员工信息
方法一：
select e.* from sal > any (select sal from emp where deptno=30)
方法二：
-- 分析：大于任意一个，只要大于最小的那个就行了 select e.* from sal > (select min(sal) from emp where deptno=30)
【3】all
ALL操作符有以下三种用法：
<>ALL：等价于NOT IN（但是=ALL并不等价于IN）
>ALL：比子查询中最大的值还要大（还包含了>=ALL）
<ALL：比子查询中最小的值还要小（还包含了<=ALL）
【3】ALL：和子查询返回的所有值比较
例子:查询工资比30号部门所有员工高的员工信息
方法一：使用子查询的any
select e.* from sal> any ( select sal from emp where deptno=30)
方法二：大于子查询的最大值即可
select e.* from sal> ( select max(sal) from emp where deptno=30)
- 在where后面使用子查询
select * from emp where sal>( select sal from emp where ename='张三' );
- 在select后面使用子查询
在select后面使用子查询必须是单行子查询
-- 错误写法，因为此时子查询是多行记录 select empno,ename,sal,(select job from emp ) --正确写法： select empno,ename,sal,(select job from emp where empno=7893) from emp;
- 在having后面使用子查询
select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30);
- 在from后面使用子查询
select * from ( select empno,ename,sal from emp )
--例一：查询员工信息:员工号，姓名，月薪 select * from (select empno,ename,sal from emp) --例二：查询员工信息:员工号，姓名，月薪，年薪 select * from (select empno,ename,sal,sal*12 as annsal from emp)
【4】exists [考]
【1】当EXISTS语句中的查询结果为非空时返回值为True
【2】当EXISTS语句中的查询结果为空时返回值为False
【3】exists是存在一个即可返回真，not exists是全都不存在才返回真
EXISTS也可进行相关的子查询，带有EXISTS的子查询不返回任何实际的数据，它只得到逻辑值‘真’或者‘假’。当子查询的结果集合为非空时，外层WHERE子句返回针织，否则返回假值。
NOT EXISTS 则相反，如果子查询的结果为非空，而外面使用了NOT EXISTS，则得到的结果为假。
-- 关于exists和 not exists SELECT EMPID,EMPNAME FROM EMPLOYEES WHERE NOT EXISTS ( SELECT EMPRESPECT FROM EMPLOYEES WHERE NOT EXISTS ( SELECT * FROM ORDERS WHERE 1=2 ) )
如果想要得到一张表的框架，而不得到表的结构，则也可以使用上面的方法：
SELECT EMPID,EMPNAME INTO TEMP123 FROM EMPLOYEES WHERE 1=2
使用嵌套EXISTS：
最后一个示例是双嵌套 NOT EXISTS查询。也就是说，它在NOT EXISTS子句中还有一个NOT EXISTS子句。在形式上，它回答了“一个城市是否存在一个不在商店里的商店”﹖但更容易说，嵌套的不存在回答了问题“X对所有Y都是真的吗?”。
SELECT A FROM RRR R1 WHERE NOT EXISTS ( SELECT B,C FROM TTT WHERE NOT EXISTS ( SELECT * FROM RRR R2 WHERE R2.B=TTT.B AND R2.C=TTT.C AND R2.A=R1.A ) )
双重否定等于肯定：
SELECT A FROM RRR R1 WHERE NOT EXISTS ( SELECT B,C FROM TTT WHERE NOT EXISTS ( SELECT * FROM RRR R2 WHERE (R2.B+R2.C)=(TTT.B+TTT.C) AND R2.A=R1.A ) )
--查询购买了所有产品的客户姓名 -- 出来的结果是所有的公司，用了三重否定 -- 如果在三层嵌套中任意两层使用not 剩下一层使用exists，则得到的就是正确的结果：即没有一家公司购买的是所有产品 SELECT CUSTNAME FROM CUSTOMERS C1 WHERE NOT EXISTS ( SELECT * FROM ORDERS O1 WHERE NOT EXISTS ( SELECT * FROM ORDERDETAIL O2 WHERE EXISTS ( SELECT * FROM PRODUCTS P WHERE P.PNAME=O2.PNAME AND O1.ORDERID=O2.ORDERID AND O1.CUSTID=C1.CUSTID ) ) )
3、子查询中的空值问题
【1】单行子查询当中的空值问题
必须使用in或者
由于条件为假，因此子查询不返回任何值。
【2】多行子查询当中的空值问题
例子：查询不是老板的员工
在emp表当中，有一列MGR，代表该员工的老板的编号
为什么not in得不到正确的结果呢：
因为not in 等价于 不等于集合的所有元素
a not in(10,20,null) -- 等价于 a != 10 and a!=20 and a!=null -- 而a!=null 这个条件永远为假 ，因为判断一个值等不等于空值， --不能使用等于或者不等于，因为永远是不等于。
因此多行子查询中如果存在空值，就不能使用not in 。但是如果子查询的结果包含空值，是可以使用in，因为这里in等同于any，any是取集合当中的一个值，不用和集合中的所有值进行比较。
在多行子查询当中，且包含空值，使用not in 的正确写法如下：
select * from emp where empno not in( select mgr from emp where mgr is not null)
