SQL 基础--> 子查询

--=========================

--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(*)外,都会忽略掉空值 */

/*

十、更多*/

Oracle 数据库实例启动关闭过程

Oracle 10g SGA 的自动化管理

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

Oracle实例和Oracle数据库(Oracle体系结构)

SQL 基础-->常用函数

SQL基础-->过滤和排序

SQL 基础-->SELECT 查询

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

3484
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2155
来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

4206
来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

682
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28211
来自专栏乐沙弥的世界

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

1203
来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3655
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2799
来自专栏乐沙弥的世界

INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是...

1162
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

723

扫码关注云+社区

领取腾讯云代金券