Oracle03--子查询

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分析问题中必须在子查询中使用排序。