minus/not in/not exists的原理和效率

http://www.itpub.net/viewthread.php?tid=984425&extra=&page=3

minus：

[

CODE:

Select *
From gal_voucher_item item
Where item_iid In (Select item_iid
                                  From gal_voucher_item
                                  Minus
                                 Select item_iid From gal_voucher_item_detail);

时间：4.438/4.39 s

not in：

Select *
From gal_voucher_item  item
Where Not Exists (Select /*+ hash_aj */ 1 From gal_voucher_item_detail detail Where item.item_iid = detail.item_iid);

时间：比上面MIUNUS快多了。

not in：

CODE:

Select * From gal_voucher_item Where item_iid Not In (Select item_iid From gal_voucher_item_detail);

时间：没跑出来

not exists:

CODE:

Select *
From gal_voucher_item item
Where Not Exists (Select 1 From gal_voucher_item_detail detail Where item.item_iid = detail.item_iid);

时间：没跑出来

评论

3 楼 UniverseSae 2011-01-01  

331008019 写道

可以考虑用这种查询方式：
Select a.*  From gal_voucher_item a, gal_voucher_item_detail b  where a.item_iid = b.item_iid(+)    and b.item_iid is null;

怎么我觉得应该是is not null?

2 楼 心似海 2010-07-28  

331008019 写道

可以考虑用这种查询方式：
Select a.*  From gal_voucher_item a, gal_voucher_item_detail b  where a.item_iid = b.item_iid(+)    and b.item_iid is null;

谢谢楼上的高手，

1 楼 331008019 2010-07-22  

可以考虑用这种查询方式：
Select a.*  From gal_voucher_item a, gal_voucher_item_detail b  where a.item_iid = b.item_iid(+)    and b.item_iid is null;