oracle子查询

子查询：当一个查询的结果时另一个查询的条件时，即嵌入在其它SQL语句中的select语句，也成为嵌套查询。

 

子查询可以返回单行结果，可以返回多行结果，也可以不返回结果

 

使用子查询注意事项：

1、子查询可以嵌套多层

2、子查询需要圆括号（）括起来

3、当在DDL语句中引用子查询时，可以带有order  by语句，但当在where子句中，set子句中引用子查询时，不能带有order  by语句

 

根据查询的结果（内部嵌套查询的结果）把子查询的类型分为单行子查询和多行子查询

 

1、单行子查询：

只返回一行数据的子查询语句

单行操作符：>   >=   <    <=   < >   =

例：SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE empno=7876)AND sal>(SELECT sal FROM emp WHERE empno=7521);

2、多行子查询：

是指返回多行数据的子查询语句，使用多行操作符：

操作符	描述
In	等于列表中的任何一个
any	子查询返回的任意一个值比较，相同的还有some
all	和子查询返回的所有值比较
Exists

 

此时不允许对其使用单行记录比较运算符

select * from emp where sal > (select avg(sal) from emp group by deptno);//非法

 

①IN操作符

SELECT * FROM emp t WHERE t.deptno IN(30,40);

②ALL操作符   如果在where条件中加入 > all ，意思是大于每一个，也就是大于最大的

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30); 

③ANY即任何一个。如果在where条件中加入>any ，意思是大于任何一个，也就是大于最小的

SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30);

④SOME即一些。和any用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同。

SELECT * FROM emp e WHERE e.sal=SOME(SELECT sal FROM emp WHERE deptno=30);

⑤exists

SELECT * FROM emp WHERE EXISTS(SELECT * FROM emp WHERE deptno=30);

 

3、多列子查询：

多列子查询返回多列结果的内部select语句，多列子查询中的列的比较有成对比较和不
成对比较，两种方法。多列子查询分为成对比较多列子查询和非成对比较多列子查询。SELECT * FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='WARD');

 

Nvl函数：如果是空值就替换成另一个值

如nvl(字段，‘x’)字段值等于null就这个函数得到的结果就是'x'，一般用于存在空值比较的情况下，比如字段a与字段b都是int型，其中一个等于null另一个为非空值，你使用a<>b是不成立的，使用此条件查询你将丢失这条本来不相等的数据，可以如此用法nvl(字段，-1)<>nvl(字段，-1)，这样就可以得到想要查询的数据，当然查询数据的前提是此字段值不能有-1值才能用这种写法

 

①成对比较

查询那些职员是工资为所任职位最高的

SELECT * FROM emp WHERE (sal,NVL(comm,-1)) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=30);

这里，子查询返回每一种职位的最高工资和职位的名称。之后主查询的每一行中的工资和职位都要与子查询返回列表中的最高工资和职位相比较，只有当两者同时完全匹配时才显示该数据行。

 

②非成对比较

若我想知道哪些职员的工资与某一职位的最高工资相同，则：

SELECT empno,ename,sal,job

FROM emp

WHERE sal IN (SELECT MAX(sal)

FROM emp

GROUP BY job)

AND job IN (SELECT DISTINCT job 

FROM emp);

这是一种典型的非成对比较的多列子查询。