该用in的时候就用in,该用exists的时候就用exists.很多开发的有个误解,就是exists比in效率高.其实是完全错误的. 给你个例子: CREATE TABLE tt AS SELECT object_id ID,object_name,s.owner FROM dba_objects s; INSERT INTO tt SELECT * FROM tt; INSERT INTO tt SELECT * FROM tt; SELECT COUNT(*) FROM tt; COMMIT; CREATE INDEX i_id ON tt(ID);SELECT * FROM TT WHERE ID IN (SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10);SELECT * FROM TT WHERE EXISTS (SELECT NULL FROM DUAL WHERE LEVEL = ID CONNECT BY LEVEL < 10); 这种情况下in就比exists效率高. in适合子查询结果集较小,并且in字段筛选性很强的情况. exists适合父表有效记录较少的情况.
select * from TABLE123 a where a.id1 in ('a','b');网上有一篇blog说的比较好的:http://www.blogjava.net/terry-zj/archive/2006/04/18/41662.html
该用in的时候就用in,该用exists的时候就用exists.很多开发的有个误解,就是exists比in效率高.其实是完全错误的.
给你个例子:
CREATE TABLE tt AS SELECT object_id ID,object_name,s.owner FROM dba_objects s;
INSERT INTO tt SELECT * FROM tt;
INSERT INTO tt SELECT * FROM tt;
SELECT COUNT(*) FROM tt;
COMMIT;
CREATE INDEX i_id ON tt(ID);SELECT *
FROM TT
WHERE ID IN (SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10);SELECT *
FROM TT
WHERE EXISTS
(SELECT NULL FROM DUAL WHERE LEVEL = ID CONNECT BY LEVEL < 10); 这种情况下in就比exists效率高. in适合子查询结果集较小,并且in字段筛选性很强的情况.
exists适合父表有效记录较少的情况.
具体有多少的差别还不是很清楚,学习。。