ORACLE学习（七）

子查询

分类：相关子查询

非相关子查询（标准子查询） 单行单列 --一个值

单列多行 ---一列值

多列 --一个表

相关子查询和非相关子查询的区别？

1、非相关子查询的子查询结果供主查询使用 子查询先于主查询执行

子查询可以单独执行

2、相关子查询的子查询和主查询相互依赖 主查询先于子查询执行

子查询不可以单独执行

--单行单列 --一个值

--查询和SMITH同部门的员工信息

SELECT *

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');

--查询和SCOTT工资一样的员工信息 结果不显示SCOTT

SELECT *

FROM EMP

WHERE SAL=(SELECT SAL FROM EMP WHERE ENAME='SCOTT')

AND ENAME!='SCOTT';

--查询和7788号员工首字母相同的员工信息

SELECT *

FROM EMP

WHERE SUBSTR(ENAME,1,1)=(SELECT SUBSTR(ENAME,1,1) FROM EMP

WHERE EMPNO=7788);

--查询工资大于全表平均工资的员工信息

SELECT *

FROM EMP

WHERE SAL>(SELECT AVG(SAL) FROM EMP);

--单行多列（一列值）

单行比较符 > < >= <= = !=

多行比较运算符ANY（任意一个） ALL(所有) in exists 存在

any和all要配合单行比较符使用

>ANY 大于最小的 >ALL 大于最大的

--查询比20部门呢所有人的工资都要高的员工信息

SELECT *

FROM EMP

WHERE SAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=20);

SELECT *

FROM EMP

WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20);

--查询比JONES所在的部门呢任意一个人的工资低的员工信息

SELECT *

FROM EMP

WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM EMP WHERE ENAME='JONES'));

--查询比7654号员工所在的部门所有人的工资低的员工信息

SELECT *

FROM EMP

WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM EMP WHERE EMPNO=7654));

--查询和KING的下属同工作的员工信息

SELECT *

FROM EMP

WHERE JOB IN(SELECT JOB FROM EMP WHERE MGR=

(SELECT EMPNO FROM EMP WHERE ENAME='KING'));

EXISTS 存在 可以用到非相关子查询 但是更多引导的是相关子查询

--查询emp的信息

SELECT *

FROM EMP

WHERE EXISTS(SELECT * FROM EMP WHERE 1=2)

注：exists引导非相关子查询 当子查询有结果时 才去执行主查询

子查询没有结果 那么主查询结果也为空

相关子查询一定有关联条件

--查询部门所在地在NEWYORK的员工信息

SELECT * FROM EMP E

WHERE EXISTS(SELECT 1 FROM DEPT D WHERE E.DEPTNO=D.DEPTNO

AND D.LOC='NEW YORK');

--查询部门名称是SALES的员工信息

非相关：

SELECT * FROM EMP

WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

相关

SELECT * FROM EMP E

WHERE EXISTS(SELECT 2 FROM DEPT D WHERE E.DEPTNO=D.DEPTNO

AND D.DNAME='SALES');

--查询经理是SCOTT或者BLAKE的员工信息

非相关

SELECT * FROM EMP

WHERE MGR IN(SELECT EMPNO FROM EMP WHERE ENAME IN('SCOTT','BLAKE'));

相关

SELECT * FROM EMP E

WHERE EXISTS(SELECT 3 FROM EMP D WHERE E.MGR=D.EMPNO AND

D.ENAME IN('SCOTT','BLAKE'));

--多列子查询

--查询部门平均工资最高的部门的员工信息

SELECT * FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO FROM (SELECT DEPTNO,AVG(SAL) PJ FROM EMP

GROUP BY DEPTNO)

WHERE PJ=(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO))

--with as 把一个select语句的结果看成是一张表

语法：

WITH 表名A AS (子查询语句),表名B AS (子查询语句)

SELECT ....

WITH A AS (SELECT DEPTNO,AVG(SAL) 平均工资 FROM EMP GROUP BY DEPTNO)

SELECT * FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO FROM A

WHERE 平均工资=(SELECT MAX(平均工资) FROM A));

in的用法

1、in(值1,值2。。。)

2、in+单列多行子查询

--查询是经理的员工信息

SELECT * FROM EMP

WHERE EMPNO IN(SELECT MGR FROM EMP);

in的特殊用法

in+多列子查询

--查询和SCOTT同部门同职位的员工信息

SELECT * FROM EMP

WHERE (DEPTNO,JOB) IN(SELECT DEPTNO,JOB FROM EMP

WHERE ENAME='SCOTT');

--查询每个部门工资最低的员工信息

SELECT * FROM EMP

WHERE (DEPTNO,SAL) IN(SELECT DEPTNO,MIN(SAL) FROM EMP

GROUP BY DEPTNO);