首页 > Linux操作系统 > Linux操作系统 > Ask Tom "IN & EXISTS"

Ask Tom "IN & EXISTS"

原创 Linux操作系统 作者:zecaro 时间:2011-03-22 22:09:56 0 删除 编辑
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. See

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量