ORACLE子查询

全篇重点

一.概念

当一个查询是另一个查询的条件时.称之为子查询.

说明: 主查询的条件要啥,子查询返回的结果就要是啥

二.分类

所有的子查询必须在()中编写代码.子查询分为以下三类:

1.单列子查询:返回一列记录

2.单行子查询:返回一行记录

3.多行子查询:返回多行记录

4.多列子查询:返回多列记录

三.语法

SELECT select_list FROMtable

WHERE expression operator (sqlstatement) ;

参数说明:

1.expression 条件列

2.comparison 比较运算

3.sql statement 子查询语句

4.operator运算符,有如下几种情况:

(1)comparison (sql statement) ;

(2)comparison

{ANY | ALL |SOME} (sqlstatement);

{NOT} IN (sql statement);

{NOT} EXISTS (sql statement);

注意:子查询中不能ORDER BY分组语句。应该先处理子查询，再处理父查询。

(一)单行子查询(简单嵌套查询)

1.概念

子查询的返回值只能是一行或零行,且子查询跟随在 =、!=、<、<=、>、>= 之后.

2.语法

SELECT select_list

FROM table

WHERE expression comparison (sql statement) ;

4.范例

(1)要求查询出比7654工资要高的全部雇员的信息

SELECT * FROM emp

WHERE sal>(SELECT salFROM emp WHERE empno='7654');

(2)要求查询出工资比7654高,同时与7788从事相同工作的全部雇员信息

分析:两个条件

SELECT * FROMemp

WHERE sal>(SELECT salFROMemp WHERE empno=7654)

AND job=(SELECT jobFROM emp WHERE empno=7788);

(3)要求查询出工资最低的雇员姓名,工作,工资

分步:

a.求出最低工资

SELECTMIN(sal)FROM emp;

b.以最低工资为条件,进行下一步查询

SELECT * FROMempWHERE sal=(SELECTMIN(sal) FROMemp);

(4)思考:要求查询出:部门名称,部门员工数,部门平均工资,部门的最低收入雇员的姓名,需要两张表关联:dept,emp

分步:

a.如果想要求出每个部门的员工数量,及平均工资,则肯定要使用分组统计,按deptno进行分组.

SELECTdeptno,COUNT(empno),AVG(sal)

FROMemp

GROUP BY deptno;

b.但是如果想要查出来部门的名称,则需要与dept表进行关联

SELECT d.dname,ed.c,ed.a

FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a

FROM emp GROUP BY deptno) ed

WHERE d.deptno=ed.deptno;

c.求出部门的最低收入雇员的姓名

SELECT d.dname,ed.a,e.ename

FROM dept d,AVG(sal) a,

MIN(sal) mins

FROM emp

GROUP BY deptno) ed,emp e

WHERE d.deptno=ed.deptno AND e.sal=ed.mins;

(二)多行子查询

1.概念:

多行子查询可以返回单列多行的数据. 在这种多行子查询中，必须使用多行运算符IN,ANY,ALL来判断，而不能使用单行运算符. 使用多行运算符可以执行与一个或多个数据的比较操作。

2.在Oracle系统中,可以使用的多行比较运算符包括：

IN:等于列表中的任何一值

ANY与子查询返回的每一个值进行比较

ALL与子查询返回的所有值进行比较

详细说明:

(1)IN NOTIN

只要主查询中列或运算式是在（不在）子查询所得结果列表中的话，则主查询的结果为我们要的数据.

(等于列表中的任何一值)

范例:求出每个部门的最低工资的雇员信息

SELECT * FROMemp

WHERE sal IN(SELECTMIN(sal) FROM empGROUP BY deptno);

(2) ANY

ANY运算符表示与子查询中的每一个值进行比较。这时，需要将单行比较运算符与该运算符组合起来使用与单行比较运算符组合之后，所使用的ANY运算符结果如下： 

1.=ANY:与IN功能完全一样

SELECT * FROMemp

WHERE sal =ANY(SELECTMIN(sal) FROM empGROUP BY deptno);

2.>ANY:比()内最小的值要大

SELECT * FROMemp

WHERE sal >ANY(SELECTMIN(sal) FROM empGROUP BY deptno);

3.<ANY:比()内最大的值要小

SELECT * FROMemp

WHERE sal <ANY(SELECTMIN(sal) FROM empGROUP BY deptno);

(3) ALL

对于ALL运算符而言，与单行比较运算符组合之后，所使用的ANY运算符结果如下： 

1.>ALL:比最大的值要大

SELECT * FROMemp

WHERE sal >ALL(SELECTMIN(sal) FROM empGROUP BY deptno);

2.<ALL:比最小的值要小

SELECT * FROMemp

WHERE sal <ALL(SELECTMIN(sal) FROM empGROUP BY deptno);

四.补充

(一)多列子查询

1.概念

进行成对比较

2.语法:

略

3.范例:

(1)略

SELECT * FROMemp

WHERE(sal,NVL(comm,-1))IN ( SELECT sal,NVL(comm,-1)FROM emp

WHEREdeptno=20);

(2)查询工资为部门最高的记录

SELECT *FROM scott.emp

WHERE(sal,job) IN (SELECT MAX(sal),job FROMscott.emp GROUP BY job);