--=========================
--SQL 基础 --> 子查询
--=========================
/*
一、子查询
子查询就是位于 SELECT 、 UPDATE 、或 DELETE 语句中内部的查询
二、子查询的分类
单行子查询
返回零行或一行
多行子查询
返回一行或多行
多列子查询
返回多列
相关子查询
引用外部 SQL 语句中的一列或多列
嵌套子查询
位于其它子查询中的查询
三、子查询语法 */
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list
FROM table );
/*
子查询 ( 内部查询 ) 在执行主查询之前执行一次
然后主查询 ( 外部查询 ) 会使用该子查询的结果
四、子查询的规则
将子查询括在括号中
将子查询放置在比较条件的右侧
只有在执行排序 Top-N 分析时，子查询中才需要使用 ORDER BY 子句
单行运算符用于单行子查询，多行运算符用于多行子查询
五、单行子查询
仅返回一行
使用单行的表较运算符： = ,>, >= ,< , <= ,<>
在 WHERE 子句中使用子查询 */
SQL > select ename , job from emp
2 where empno = (
3 select empno from emp
4 where mgr = 7902 );
ENAME JOB
---------- ---------
SMITH CLERK
-- 使用分组函数的子查询
SQL > select ename , job , sal
2 from emp
3 where sal >
4 ( select avg ( sal ) from emp );
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
-- 在 HAVING 子句中使用子查询
SQL > select deptno , min ( sal )
2 from emp
3 group by deptno
4 having min ( sal ) >
5 ( select min ( sal )
6 from emp
7 where deptno = 20 );
DEPTNO MIN ( SAL )
---------- ----------
30 950
10 1300
-- 在 FROM 子句中使用子查询
SQL > select empno , ename
2 from
3 ( select empno , ename
4 from emp
5 where deptno = 20 );
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
-- 单行子查询中的常见错误
-- 子查询的结果返回多于一行
SQL > select empno , ename
2 from emp
3 where sal =
4 ( select sal
5 from emp
6 where deptno = 20 );
( select sal
*
ERROR at line 4 :
ORA - 01427 : single - row subquery returns more than one row
-- 子查询中不能包含 ORDER BY 子句
SQL > select empno , ename
2 from emp
3 where sal >
4 ( select avg ( sal )
5 from emp
6 order by empno );
order by empno )
*
ERROR at line 6 :
ORA - 00907 : missing right parenthesis
-- 子查询内部没有返回行，如下语句可以正确执行，但没有数据返回
SQL > select ename , job
2 from emp
3 where empno =
4 ( select empno
5 from emp
6 where mgr = 8000 );
no rows selected
/*
六、多行子查询
返回多个行
使用多行比较运算符 IN ,ANY ,ALL
在多行子查询中使用 IN 操作符 */
SQL > select empno , ename , job
2 from emp
3 where sal in
4 ( select max ( sal )
5 from emp
6 group by deptno );
EMPNO ENAME JOB
---------- ---------- ---------
7698 BLAKE MANAGER
7902 FORD ANALYST
7788 SCOTT ANALYST
7839 KING PRESIDENT
-- 在多行子查询中使用 ANY 操作符
SQL > select empno , ename , job
2 from emp
3 where sal < any
4 ( select avg ( sal )
5 from emp
6 group by deptno );
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7900 JAMES CLERK
7876 ADAMS CLERK
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7934 MILLER CLERK
7844 TURNER SALESMAN
7499 ALLEN SALESMAN
7782 CLARK MANAGER
7698 BLAKE MANAGER
-- 在多行子查询中使用 ALL 操作符
SQL > select empno , ename , job
2 from emp
3 where sal > all
4 ( select avg ( sal )
5 from emp
6 * group by deptno )
EMPNO ENAME JOB
---------- ---------- ---------
7566 JONES MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
/*
七、相关子查询
子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询 */
-- 查询工资高于同一部门的员工的部门号，姓名，工资
SQL > select deptno , ename , sal
2 from emp outer
3 where sal >
4 ( select avg ( sal )
5 from emp inner
6 where inner. deptno = outer. deptno );
DEPTNO ENAME SAL
---------- ---------- ----------
30 ALLEN 1600
20 JONES 2975
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
-- 查询负责管理其它员工的员工记录 ( 使用 exists)
SQL > select empno , ename
2 from emp outer
3 where exists
4 ( select empno
5 from emp inner
6 where inner. mgr = outer. empno );
EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7902 FORD
-- 查询不管理其它员工的职员 (not exists)
SQL > l3
3 * where exists
SQL > c / where / where not
3 * where not exists
SQL > l
1 select empno , ename
2 from emp outer
3 where not exists
4 ( select empno
5 from emp inner
6 * where inner. mgr = outer. empno )
SQL > /
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7876 ADAMS
7900 JAMES
7934 MILLER
EXISTS 和 NOT EXISTS 与 IN 和 NOT IN 的比较
EXISTS 与 IN 的不同：
EXISTS 只检查行的存在性 ,IN 要检查实际值的存在性 ( 一般情况下 EXISTS 的性能高于 IN)
NOT EXISTS 和 NOT IN
当值列表中包含空值的情况下 ,NOT EXISTS 则返回 true , 而 NOT IN 则返回 false .
-- 看下面的查询，查询部门号不在 emp 表中出现的部门名称及位置
SQL > select deptno , dname , loc
2 from dept d
3 where not exists
4 ( select 1
5 from emp e
6 * where e . deptno = d . deptno )
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
--IN 与空值
SQL > SELECT *
2 FROM emp e
3 WHERE e . empno NOT IN (
4 SELECT 7369 FROM dual
5 UNION ALL
6 SELECT NULL FROM dual
7 )
8 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL > SELECT *
2 FROM emp e
3 WHERE e . empno IN ( '7369' ,NULL)
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980 - 12 - 17 800.00 20
/*
注：子查询要包含在括号内
子查询一般放在比较条件的右侧
除非进行 TOP － N 分析，否则不要在子查询中使用 ORDER BY 。 */
/*
八、多列子查询
1 、成对比较
查询工资为部门最高的记录 */
SQL > select * from scott . emp
2 where ( sal , job ) in
3 ( select max ( sal ), job from scott . emp group by job );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23 - JAN - 82 1300 10
7499 ALLEN SALESMAN 7698 20 - FEB - 81 1600 300 30
7839 KING PRESIDENT 17 - NOV - 81 5000 10
7566 JONES MANAGER 7839 02 - APR - 81 2975 20
7902 FORD ANALYST 7566 03 - DEC - 81 3000 20
7788 SCOTT ANALYST 7566 19 - APR - 87 3000 20
/*
2 、非成对比较 , 实现了与上述类似的功能 */
SQL > select * from scott . emp
2 where sal in ( select max ( sal ) from scott . emp group by job )
3 and job in ( select distinct job from scott . emp );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23 - JAN - 82 1300 10
7499 ALLEN SALESMAN 7698 20 - FEB - 81 1600 300 30
7566 JONES MANAGER 7839 02 - APR - 81 2975 20
7788 SCOTT ANALYST 7566 19 - APR - 87 3000 20
7902 FORD ANALYST 7566 03 - DEC - 81 3000 20
7839 KING PRESIDENT 17 - NOV - 81 5000 10
/*
九、嵌套子查询
即位于子查询内部的子查询，嵌套层数最多可达层。然而应尽量避免使用嵌套子查询，使用表连接的查询性能会更高 */
SQL > select deptno , Num_emp
2 from ( select deptno , count ( empno ) as Num_emp from emp group by deptno ) d
3 where Num_emp > 3 ;
DEPTNO NUM_EMP
---------- ----------
30 6
20 5
/*
注意：子查询对空值的处理
除了 count(*) 外，都会忽略掉空值 */
/*
十、更多 */
使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 实例
Oracle 实例和 Oracle 数据库 (Oracle 体系结构 )
