Oracle11G-SQL开发指南-6-子查询

子查询(subquery):在外部的SELECT、UPDATE或DELETE语句内部使用的SELECT语句。

1.  子查询的类型
    1> 单行子查询 
       不向外部的SQL语句返回结果，或者只返回一行。
       标题子查询(scalar subquery):单行子查询的一种特殊情况，返回一行且精确的包含一列。
       单行子查询最多只能返回一行；
       单行子查询不能使用order by 语句；

    2> 多行子查询
       向外部SQL语句返回一行或多行；
       外部查询可以使用IN、ANY或ALL操作符；

    3> 多列子查询
       向外部SQL语句返回多列
    
    4> 关联子查询
       引用外部的SQL语句中的一列或多列，通过相同的列与外部的SQL语句相关联。
    
    5> 嵌套子查询
       位于另一个子查询中，子查询最多可以嵌套255层。 
     
2.  编写单行子查询
    1> 在where子句中使用子查询
       select * from customers where customer_id = (select customer_id from customers where last_name='abc' );
    
    2> 使用其他单行操作符
       > 、<、<>、>=、<=、
    
    3> 在HAVING子句中使用子查询

    4> 在FROM子句中使用子查询(内联视图 inline view)

3.  编写多行子查询
    1> 在多行子查询中使用(not) in操作符，用来检查一个值列表中是否包含指定的值
    2> 在多行子查询中使用ANY操作符。用来将一个值与一个列表中的任何值进行比较，在ANY前必须使用一个=、>、<、>=、<=操作符;
        select * from employees where salary < any ( select low_salary from salary_grades ) ;
    3> 在多行子查询中使用ALL操作符，用来将一个值与一个列表中的所有值进行比较，在ALL前必须使用一个=、>、<、>=、<=操作符;

4.  编写多列子查询。编写返回多列的子查询
     -- 用来检索每种产品类型中的价格最低的产品
     select product_id, product_type_id, name, price
       from products
      where (product_type_id, price) in
            (select product_type_id, min(price)
               from products
              group by product_type_id);

5.  编写关联子查询
    1> 普通的关联子查询
    -- 用来检索价格高于同类产品平均价格的产品
    select product_id, product_type_id, name, price
      from products
     outer where price >
              (select avg(price)
                 from products
                inner where inner.product_type_id = outer.product_type_id);

    2>  在关联子查询中使用 EXISTS 和 NOT EXISTS
        

注：in与not in,exists与not exists的区别
    1、in和exists 
       IN:确定给定的值是否与子查询或列表中的值相匹配。是把外表和内表作hash连接，
       EXISTS:指定一个子查询，检测行的存在。是对外表作loop循环，每次loop循环再对内表进行查询，
       如果查询的两个表大小相当，那么用in和exists差别不大；
       如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

      例如：表A(小表)，表B(大表)
        select * from A where cc in(select cc from B);　　-->效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where B.cc=A.cc);　　-->效率高，用到了B表上cc列的索引。
        相反的：
select * from B where cc in(select cc from A);　　-->效率高，用到了B表上cc列的索引
select * from B where exists(select cc from A where B.cc=B.cc);　　-->效率低，用到了A表上cc列的索引。

    2、not in 和not exists
       not in 逻辑上不完全等同于not exists，因为not in对于null值的处理特殊：
       例:
        create table #t1(c1 int,c2 int);
create table #t2(c1 int,c2 int);
insert into #t1 values(1,2);
insert into #t1 values(1,3);
insert into #t2 values(1,2);
insert into #t2 values(1,null); 

        select * from #t1 where c2 not in(select c2 from #t2);　　-->执行结果：无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)　　-->执行结果：1　　3
        正如所看到的，not in出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select 语句的执行计划，也会不同，后者使用了hash_aj，
所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。
如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。
如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。
        如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；
而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。
    
        exist,not exist一般都是与子查询一起使用. 
In可以与子查询一起使用,也可以直接in (a,b.....)

        exist:会针对子查询的表使用索引. 
not exist:会对主子查询都会使用索引. 
in:与子查询一起使用的时候,只能针对主查询使用索引. 
not in:则不会使用任何索引. 

    3、in 与 = 的区别
        select name from student where name in('zhang','wang','zhao');
        与
        select name from student where name='zhang' or name='wang' or name='zhao';
        的结果是相同的。
     exists()后面的子查询被称做相关子查询，不返回列表的值的.只是返回一个ture或false的结果,是否存在。
     (这也是为什么子查询里是“select 1”的原因,换成“select 6"完全一样,当然也可以select字段,但是明显效率低些)
        其运行方式是先运行主查询一次,再去子查询里查询与其对应的结果,如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.    
    in()后面的子查询是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

从sql编程角度来说, in直观,exists不直观多一个select, in可以用于各种子查询, 而exists好像只用于关联子查询
从性能上来看exists是用loop的方式,循环的次数影响大,外表要记录数少,内表就无所谓了
in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式。



1.exists的执行流程
  select * from t1 where exists (select null from t2 where t2.y = t1.x);
  可以理解为：
  for x in ( select * from t1)
      loop 
          if( exists (select null from t2 where t2.y = t1.x )
             then 
      output the record
 end if
       end loop