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

最新推荐文章于 2022-01-31 13:00:00 发布

不问因果

最新推荐文章于 2022-01-31 13:00:00 发布

阅读量500

点赞数

分类专栏： oracle，sql 文章标签： oracle

本文链接：https://blog.csdn.net/lzhat/article/details/58607941

版权

oracle，sql 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

子查询(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