使用子查询检索数据

一、相关子查询

相关子查询对于外部查询的每一个值都会有一个结果与其对应，其计算的过程是这样的：
1、扫描外查询的第一条记录
2、扫描子查询，并将第一条记录的对应值传给子查询，由此计算出子查询的结果
3、根据子查询的结果，返回外查询的结果。
4、重复上述动作，开始扫描外查询的第二条记录，第三条记录，直至全部扫描完毕
例句：

--查询本部门最高工资的员工?
--方法一，使用嵌套子查询（非关联子查询）
select * from emp a where (a.deptno,a.sal) in (select deptno,max(sal) from emp group by deptno);

--方法二，使用关联子查询
select * from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno);

二、EXISTS

exists是判断 exits 后面的 sql 语句是否为真,若为真则整个sql句子成立，否则没有任何记录。
例句：

SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM DUAL WHERE 2 = 1);

三、WITH

语法：

SELECT *
  FROM (--模拟生一个20行的数据
        SELECT LEVEL AS lv
          FROM DUAL
       CONNECT BY LEVEL < 20) tt
 WHERE tt.lv > 10 AND tt.lv < 15

WITH TT AS(--模拟生一个20行的数据
           SELECT LEVEL AS lv
             FROM DUAL
          CONNECT BY LEVEL < 20) 
SELECT lv
  FROM TT
 WHERE lv > 10 AND lv < 15
WITH tempName AS (SELECT ....)
SELECT ...

说明：
　　可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理
优点：
　　增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。
例句：

--普通查询
SELECT *
  FROM (--模拟生一个20行的数据
        SELECT LEVEL AS lv
          FROM DUAL
       CONNECT BY LEVEL < 20) tt
 WHERE tt.lv > 10 AND tt.lv < 15

--with语句
WITH TT AS(--模拟生一个20行的数据
           SELECT LEVEL AS lv
             FROM DUAL
          CONNECT BY LEVEL < 20) 
SELECT lv
  FROM TT
 WHERE lv > 10 AND lv < 15