子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式。
子查询可以返回的数据类型一共分为四种:
单行单列:返回的是一个具体列的内容,可以理解为一个单值数据;
单行多列:返回一行数据中多个列的内容;
多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围;
多行多列:查询返回的结果是一张临时表;
WHERE子句:此时子查询返回的结果一般都是单行单列、单行多列、多行单列;
HAVING子句:此时子查询返回的都是单行单列数据,同时为了使用统计函数操作;
FROM子句:此时子查询返回的结果一般都是多行多列,可以按照一张数据表(临时表)的形式操作。
单行单列
查询出基本工资比ALLEN低的全部雇员信息
SELECT * FROM emp WHERE sal< (SELECT sal FROM emp WHERE ename=‘ALLEN’) ;
单行多列
查询与SCOTT从事同一工作且工资相同的雇员信息
SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename=‘SCOTT’) AND ename<>‘SCOTT’
多行单列
主要使用三种操作符:IN、ANY、ALL
IN表示在一个范围内,NOT IN表示不属于这个范围
查询出与每个部门中最低工资相同的全部雇员信息
SELECT * FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno) ;
查询出不与每个部门中最低工资相同的全部雇员信息
SELECT * FROM emp WHERE sal NOT IN (SELECT MIN(sal) FROM emp GROUP BY deptno) ;
=ANY:等价于IN
ANY:比子查询中返回结果的最小的要大
<ANY:比子查询中返回结果的最大的要小
<>ALL:等价于NOT IN
ALL:比子查询中最大的值还要大
<ALL:比子查询中最小的值还要小
EXISTS()可以判断查询结果是否为null,exists后面存在数值前面就运行,后面为NULL前面不运行
SELECT * FROM emp WHERE EXISTS( SELECT * FROM emp) ;
多行多列
子查询一般都出现在FROM子句之中(建立临时表)
要求查询出每个部门的编号、名称、位置、部门人数、平均工资
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d, (SELECT deptno dno, COUNT(empno) count ,ROUND(AVG(sal),2) avg FROM emp GROUP BY deptno) temp
WHERE d.deptno=temp.dno(+) ;