子查询
分类:相关子查询
非相关子查询(标准子查询) 单行单列 --一个值
单列多行 ---一列值
多列 --一个表
相关子查询和非相关子查询的区别?
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);