oracle查询应尽量用exists而不用in

in 和 exists 都是出现在条件中，功能相似，而且很多情况下是可以互相转化的。

in是表示某值是否在一个列表中,这种方法要进行值的真正比较运算；exists 只是证明存在性，而不会进行比较运算，相对来说效率更高。因此，能用exists的地方尽量不用in。例如：查询价格大于90的书的作者的名字

 

select first_name,last_name

from authors

where author_id in(select w.author_id

                                from write_book w join books b

                                on w.book_id=b.book_id

                                 where b.price>90);

 

select first_name,last_name

from authors a

where exists (select 1

                       from (select w.author_id

                                 from write_book w inner join books b

                                 on w.book_id=b.book_id

                                  where b.price>90) i

                       where i.author_id=a.author_id);