1. 子查询
子查询也称之为嵌套子句查询。
1.1. 语法
语法上的运行使用规则：
l 子查询 (内查询、嵌套子句) 在主查询之前一次执行完成。（子查询先执行）
l 子查询的结果被主查询使用 (外查询)。
l 子查询要包含在括号内。
l 将子查询放在比较条件的右侧。
1.2. 为什么要使用子查询？
【需求】谁的工资比scott高？
采用连接的方式写（这里是自连接，见下图）：
--【需求】谁的工资比scott高？ --多表关联查询：自连接的不等值连接 SELECT * FROM emp t1,emp t2 WHERE t2.ename='SCOTT' AND t1.sal>t2.sal
采用子查询的方式写：
--子查询 --分析一下:谁的工资比scott高？---> 1,scott工资是多少 2，谁的工资比3000高 SELECT sal FROM emp WHERE ename='SCOTT'; SELECT * FROM emp WHERE sal >3000; SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='SCOTT');
对比可以发现：在某些业务上，子查询比连接查询更容易理解。
1.3. 子查询的分类
注意：单行语句与多行语句的区别：
单行操作符（>、 =、 <）对应单行子查询，多行操作符(in、not in)对应多行子查询。
单行语句也可以使用多行语句比如in，但多行语句一定不可以使用单行操作符比如=；
1.4. 单行子查询
1.4.1. 语法要求：
1.只返回一行记录。
2.使用单行比较操作符。
其中 <> 也可以可以用 != 代替，意思一样。
--查询部门名称是SALES的员工信息
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM DEPT WHERE dname ='SALES')
了解：子查询可以是一张表的数据，也可以是不同表的数据。
1.4.2. 空值问题
--需求:查找工作和'Rose' 这个人的工作一样的员工信息 SELECT job FROM emp WHERE ename = 'Rose'; SELECT * FROM emp WHERE job =(SELECT job FROM emp WHERE ename = 'Rose'); SELECT * FROM emp; --需求:查找工作和'Rose' 这个人的工作不一样的员工信息 SELECT * FROM emp WHERE job !=(SELECT job FROM emp WHERE ename = 'Rose'); --结论: 只要子查询返回的结果是null的话, 那么主查询的结果就一定是null
注意：使用子查询的时候，一定要保证子查询不能为空，否则数据就会出现异常。
1.4.3. 非法使用单行子查询
【示例】需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作一样的员工信息
注意：子句查询返回多行数据时，主句千万不可以用单行操作符接收
1.5. 多行子查询
1.5.1. 语法要求：
l 返回多行记录。
l 使用多行比较操作符。
1.5.2. In操作符
--需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作一样的员工信息 SELECT JOB FROM emp WHERE ename IN('SMITH','ALLEN'); SELECT * FROM emp WHERE job IN(SELECT JOB FROM emp WHERE ename IN('SMITH','ALLEN')); --需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作不一样的员工信息 SELECT * FROM emp WHERE job NOT IN(SELECT JOB FROM emp WHERE ename IN('SMITH','ALLEN'));
1.5.3. Any和all操作符
--需求:查询工资比30号部门任意一个员工的工资高的员工信息。--面试题 SELECT * FROM emp WHERE deptno =30; --任意一个：比最低的那个高就ok。 SELECT * FROM emp WHERE sal >(SELECT MIN(sal) FROM emp WHERE deptno=30); --any(多行函数) SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30); --【示例】需求:查询工资比30号部门所有员工的工资高的员工信息。 SELECT * FROM emp WHERE sal>(SELECT MAX (sal) FROM emp WHERE deptno=30); --all(多个返回记录)--max(sal) SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);
分析结果：
1.6. 子查询注意事项
l 关于格式：子查询要包含在括号内，最好有合理的书写风格。
l 子查询的位置：可以放在主查询的where、select、having、from的后面。不可以放在主查询的group by后面。
l 子查询和主查询可以是同一张表，也可以不是是不同一张表，只要子查询返回的结果在主查询中能使用即可。
l 关于使用操作符：单行操作符对应单行子查询，多行操作符对应多行子查询。
l 执行顺序：一般子查询先执行，再执行主查询；
l 关于排序：一般不在子查询中使用order by；但在top-N分析问题中，必须在子查询中使用order by。
| 多行子查询一般用于from后面，作为一张新的虚拟临时表来使用。
虚拟临时表是临时表的一种，是运行过程中，内存中虚拟出来的一张临时表，用于sql的操作。
--虚拟表 SELECT * FROM ( SELECT * FROM emp WHERE deptno=30 --虚表:将查询结果再作为一张表来使用。 ) t WHERE sal>2000;
1.7. 子查询和多表关联查询的选择
理论上，在都可以实现需求的情况下尽量选择多表查询。
原因：子查询会操作两次，多表查询只操作一次。多表的效率高。
但要注意的是，多表查询如果产生了笛卡尔集（语句上要注意条件的使用），则会出现严重的效率问题。
一般不在子查询中使用排序（order by），但在top-N分析问题中必须在子查询中使用排序。