The "select * from big where object_id in ( select object_id from small )" will sort BIG once and SMALL once and join them (sort merge join) in all likelyhood.
The "select * from big where exists ( select null from small where small.object_id = big.object_id )" will tend to FULL SCAN big once and FOR EVERY ROW in big -- FULL SCAN small.
(after running it -- i verified it and the "rule of thumb" holds true. BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS. Remember -- thats is a RULE OF THUMB and rules of thumb always have infinitely many exceptions to the rule.
this explanation hold good even for NOT IN and NOT EXISTS ?
No, partially because NOT IN and NOT EXISTS are not substitutes for eachother! They mean semantically different things and can/will return different answers.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23650854/viewspace-690200/，如需转载，请注明出处，否则将追究法律责任。