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

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