替代sql的in,not in操作

select * from tableb b where exists(select 1 from tablea a where a.cola=b.col)
用EXISTS方式会比IN的方式查询速度快

 

使用外部连接代替NOT IN

原SQL

Select Title from Bookshelf where Tiltle not in(select Title from bookSelf_checkout) order by Title

 

外部连接的Sql语句

Select distinct b.title from bookshelf_checkout bc right outer join bookshelf b on bc.title=b.title where bc.title is Null order by b.title

 

使用Not Exists 来代替Not in

Select b.title from bookshelf b where not exists(select ‘x’ from bookshelf_checkout bc where bc.title=b.tiltle) order by b.title

 

(注:部分来自书籍和网络)