子查询

子查询

子查询概念 ：当一个查询的结果是另一个查询的条件时，称之为子查询。

使用子查询注意事项：

         子查询可以嵌套多层

         子查询需要圆括号()括起来

子查询和连接查询一样提供了使用单个查询访问多个表中的数据的方法，子查询在其他查询结果的基础上，提供一种进一步有效的方式来表示WHERE子句中的条件，子查询是一个select语句，它可以在select,insert,update或delete语句中使用。

单行子查询：子查询语句中只返回单行单列的结果，即返回一个常量值

多行子查询：子查询语句中返回多列结果，即返回一系列值

子查询必须用括号括起来，否则无法判断语句的开始和始末，子查询可以出现在where和 from 语句中，子查询可以嵌套多层，子查询语句中不能包含order by 子句，而外查询可以有有一个order by 子句。

子查询语法：

SELECT     select_list

FROM       table

WHERE    expr operator

                           (SELECT    select_list

                          FROM                  table);

l       子查询 (内查询) 在主查询之前一次执行完成。

l       子查询的结果被主查询使用 (外查询)。

IN关键字：

使用IN关键字可以将原表中特定列的值和子查询返回的结果集的值进行比较，如果特定的值和子查询节结果相同，则select语句中的查询结果中就包含着一行。

Select 商品编号，商品名称，单价

From 商品信息

Where 供应商编号 IN(

Select 供应商编号 from 供应商信息

Where 供应商所在城市=’北京市’

)

该查询语句执行顺序是：首先执行括号内的子查询，然后在实行外层查询。

EXISTS关键字

在一些情况下，只需要子查询返回一个真值或者假值，如果只需要是否满足判断条件，而数据本身并不重要，可以使用EXISTS关键字来定义子查询，EXISTS关键字只注重于子查询是否返回行，如果子查询返回一个或多个行，那么EXISTS便返回为真，否则为假。

要使EXISTS关键字有用，应该在子查询中建立搜索条件，以便匹配子查询连接起来的两个表中的值，此处的搜索条件和连接操作中使用的连接条件相似。

Select  t1.商品编号，t1.商品名称，t1.单价

From 商品信息 t1

Where Exists（

Select * from 供应商信息 t2

Where  t2.供应商所在城市=’北京市’,

        AND  t1.供应商编号=t2.供应商编号）

Order by  t1.商品编号；

SAME,ANY,ALL关键字：

SOME,ANY,ALLL都是SQL支持的在子查询中进行比较的关键字，ANY和SOME的返回值相同，他们将根据每一行特定列的值与子查询的结果进行比较，如果每一行的比较结果都为真，则满足条件返回改行，这里将两个关键字统一为ANY。

Select 出库单编号，商品编号，出库单价，出库数量

From  出库单明细信息

Where  出库单商品金额>ANY（

         Select  入库商品金额 FROM 入库单明细信息

Where  入库数量>20）；

使用ALL关键字引入子查询的方法和使用ANY或SOME关键字一样，他把列值与子查询的结果进行比较，但是其要求所有列的查询结果都为真，而不是任意结果值的列值为真，否则，不返回行。

Select 出库单编号，商品编号，出库单价，出库数量

From  出库单明细信息

Where  出库商品信息>ALL(

         Select 入库商品金额 from 入库单明细信息

         Where  入库数量>20);

 

//子查询含有组函数

SQL> select * from emp where sal>(select avg(nvl(sal,0)) from emp);

//子查询含有having子句 查询部门的最小工资大于20号部门最小工资的部门号及最小工资数

SQL> select deptno,min(sal) from emp group by deptno having min(sal)>( select min(sal) from emp where deptno=20);

 

备注：子查询可以返回空行 没要查询到结果是可以的。

 

多行子查询

l         返回多行。

l        使用多行比较操作符。

操作符如下图：

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

 

//查询薪水小于工作岗位CLERK的任何一个薪资的员工信息 并且不包含工作岗位为CLERK的员工信息

SQL> select * from emp where sal < any (select sal from emp where job='CLERK') and job<>'CLERK';

//all与所有值比较 >all 代表的是大于查询结果的最大值

SQL> select * from emp where sal > all (select sal from emp where job='CLERK') and job<>'CLERK';

//查询岗位与部门编号为10相同的员工信息 不包含自己。

SQL> select * from emp where job in(select job from emp where deptno=10) and deptno<>10;

 

只做思考?

 

SQL> select * from emp where exists(select * from dept);

 

EMPNO ENAME  JOB        MGR HIREDATE       SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH   CLERK   7902 1980-12-17    800.00               20

 7499 ALLEN   SALESMAN  7698 1981-2-20   1600.00    300.00     30

 7521 WARD   SALESMAN  7698 1981-2-22   1250.00    500.00     30

3 rows selected

 

SQL> select * from emp where exists(select * from dept where deptno=80);

 

EMPNO ENAME  JOB        MGR HIREDATE       SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------