Oracle 子查询 返回值单行单列，单行多列，多行单列（操作符in、any、all），多行多列

子查询

查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询的综合体；

子查询：指的就是在一个查询之中嵌套了其他的若干查询

• 理论上子查询可以出现在查询语句的任意位置上，但是实际开发运用中，子查询出现在WHERE和FROM子句之中较多；

1.普通子查询（非关联子查询）

一般情况下：

WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据；

FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

HAVING：子查询返回的一般是单行单列数据。（了解）

1.1子查询返回单行单列数据

• 子查询在WHERE

--查询出工资比SMITH还要高的全部雇员信息

1. 要想完成本程序，首先必须要知道SMITH的工资是多少：
   SELECT sal FROM emp WHERE ename='SMITH';
--返回结果：（单行单列）
		SAL
	1	800.00
	
2. 由于此时返回的是**单行**的数据，所以这个子句查询可以在WHERE中出现。
   SELECT * FROM emp
   WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');

--查询出高于公司平均工资的全部雇员信息

1. 公司的平均工资应该使用AVG()函数求出。
   SELECT AVG(sal) FROM emp;
--返回结果：（单行单列）
		AVG（SAL）
	1	2073.21428571429
	
2. 此时数据的返回结果是单行单列的数据，可以在WHERE之中出现。
   SELECT * FROM emp
   WHERE sal>(SELECT AVG(sal) FROM emp);

1.2子查询返回 单行多列 数据 （了解，少用）

--查询出emp表中 职位和薪资 与雇员  'SCOTT'的 职位和薪资 相同的雇员信息

1. 求出 另一张表(假设另一张表也是emp表)的 雇员姓名 'SCOTT'的job和sal 的信息
   SELECT job,sal
   FROM emp
   WHERE ename='SCOTT';
--返回结果：（单行多列）
		SAL		JOB
	1	3000	ANALYST
	
2. 此时数据的返回结果是单行多列的数据，可以在WHERE之中出现。
   SELECT * FROM emp
   WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT');

1.3子查询返回 多行单列 数据

子查询返回多行单列数据时，需要使用三种判断符判断：IN、ANY、ALL；

判断符	作用
[NOT] IN	用于指定一个子查询的判断范围
ANY	与每一个内容想匹配，有三种匹配形式(=ANY、>ANY、<ANY)
ALL	ALL操作符：与每一个内容相匹配，有两种匹配形式：(>ALL、<ALL)

1.3.1IN 操作符

--查询和经理薪资一样的员工信息

1. 查询经理薪资:
SELECT sal FROM emp WHERE job='MANAGER' 

--返回结果：(多行多列)
		SAL
	1	2075
	2	2850
	3	2450
	
2. 薪资和 manager一样的 
   SELECT * FROM emp
   WHERE sal in (SELECT sal FROM emp WHERE job='MANAGER');

在使用 IN 的时候还要注意 NOT IN 的问题,如果使用 NOT IN 操作，在子查询之中，如果有一个内容是 null，则不会查询出任何的结果。
  
   SELECT * FROM emp
   WHERE sal not in (SELECT sal FROM emp WHERE job='MANAGER');
  
   --解决非空问题，使用空判断NVL。
   SELECT * FROM emp
   WHERE sal not in (SELECT NVL(sal,0) FROM emp WHERE job='MANAGER');

1.3.2ANY操作符

1. =ANY：功能与IN操作符是完全一样的

SELECT * FROM emp
WHERE sal =ANY( SELECT sal FROM emp WHERE job='MANAGER');

2. >ANY：比子查询中返回记录最小的还要大的数据 (大于任意数据)

SELECT * FROM emp
WHERE sal >ANY( SELECT sal FROM emp WHERE job='MANAGER');

3. <ANY：比子查询中返回记录的最大的还要小 （小于任意数据）

SELECT * FROM emp
WHERE sal <ANY( SELECT sal FROM emp WHERE job='MANAGER');

1.3.3ALL操作符

如果有一个内容是null，则不会查询出任何的结果

1. >ALL：比子查询中返回的最大的记录还要大

SELECT * FROM emp
WHERE sal >ALL(SELECT sal FROM emp WHERE job='MANAGER');

2. <ALL：比子查询中返回的最小的记录还要小

SELECT * FROM emp
WHERE sal <ALL(SELECT sal FROM emp WHERE job='MANAGER');

1.4子查询查询返回多行多列

1.4.1在FROM 中出现

在FROM子句中出现的查询，这个子查询一般返回的是多行多列的数据，当作一张临时表的方式来处理

1.4.1.1好处

• 通过建立一张临时表，可以减少查询的次数

  • emp14条数据；dept4条数据，合计56条记录
  • 临时表方法，26条记录

  假如使用emp建立一个临时数据表需要14条记录，返回3条数据的表temp
  temp（3） * dept（4） =  12；
  在建立temp的14条记录 + （temp（3） * dept（4））  =   26条记录
  
  SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
  FROM dept d,(
  SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
  FROM emp
  GROUP BY deptno) temp
  WHERE d.deptno=temp.dno(+);
  

1.4.2HAVING 中出现

一般在条件判断出现 统计函数的时候才使用

--查询出职位的名称、人数、平均薪资，且要求 职位平均薪资高于公司平均薪资的，查询结果根据平均薪资降序排列

1. 公司的平均工资应该使用AVG()函数求出。
   SELECT AVG(sal) FROM emp;

2. 此时数据的返回结果是单行单列的数据，可以在 HAVING 之中出现。
   SELECT job,COUNT(*),AVG(sal) 平均薪资
   FROM emp
   GROUP BY job
   HAVING AVG(sal) >(SELECT AVG(sal) FROM emp)
   ORDER BY 平均薪资 DESC;