欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-12 21:11:47
Oracle11G-SQL开发指南-6-子查询,子查询(subquery):在外部的SELECT、UPDATE或DELETE语句内部使用的SELECT语句。 1. 子查询的类型...

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