子查询是一张临时表
1.查询比SMITH工资高的信息
SELECT E1.* FROM EMP E1,(SELECT * FROM EMP WHERE ENAME='SMITH') E2 WHERE E1.SAL>E2.SAL;
SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH') ORDER BY SAL;
2.查询位于DALLAS部门的员工信息
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');
3.查询与SMITH在同一部门并比ALEEN高的员工信息
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH' ) AND SAL>(SELECT SAL FROM EMP WHERE ENAME='ALEEN');
多行子查询
IN 包含其中的任意一个
ANY 任意一个, OR ..OR..OR A<ANY(1,2,3) A<1 OR A<2 OR A<3 ==> A<3
ALL 匹配所有条件,最 AND ..AND ..AND A<ALL(1,2,3) A<1 AND A<2 AND A<3 ==>A<1
查询每个部门薪水最高的员工信息
SELECT * FROM EMP WHERE SAL IN(SELECT DISTINCT MAX(SAL) FROM EMP GROUP BY DEPTNO );
查询20部门比30部门所有薪水工资低的信息
SELECT * FROM EMP WHERE DEPTNO=20 AND SAL<ALL(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);
查询20部门比30部门任意薪水工资低的信息
SELECT * FROM EMP WHERE DEPTNO=20 AND SAL<ANY(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);
相关子查询
子查询涉及到父查询的数据,会先进行父查询,获取到涉及到的数据,进行子查询,最后对子查询得出的条件进行判断
查询每个部门员工工资比每个部门平均工资高的员工
SELECT * FROM EMP E WHERE SAL>(SELECT SAL FROM EMP P WHERE P.DEPTNO=E.DEPTNO );
自查询
查询SMITH的上级信息
SELECT * FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE ENAME='SMITH');
SELECT P.* FROM EMP E,EMP P WHERE E.MGR=P.EMPNO AND E.ENAME='SMITH';
查询SMITH的工资等级
SELECT GRADE FROM SALGRADE WHERE (SELECT SAL FROM EMP WHERE ENAME='SMITH') BETWEEN LOSAL AND HISAL;
SELECT E.*,S.GRADE 工资等级 FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.ENAME='SMITH';