勤加练习，必能得心应手！不期速成，日拱一卒 ！ 态度决定一切
IN

EXISTS

SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id

-- Or, using the IN clause:

SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles

pub_name
----------------------------------------
Algodata Infosystems
New Moon Books

exits 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合.

1)性能上的比较

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

select * from t1 where exists ( select null from t2 where y = x )

for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

1：
select * from A where cc in (select cc from B)

select * from A where exists(select cc from B where cc=A.cc)

2：
select * from B where cc in (select cc from A)

select * from B where exists(select cc from A where cc=B.cc)

not in 和not exists

in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'的结果是相同的。
posted on 2009-03-31 13:08 David1228 阅读(4273) 评论(0)  编辑  收藏 所属分类: 数据库

 只有注册用户登录后才能发表评论。 网站导航: 相关文章:

 < 2009年3月 >
22232425262728
1234567
891011121314
15161718192021
22232425262728
2930311234

•

• 积分 - 346490
• 排名 - 154