select b.bookrecno as bookrecno,b.isbn as isbn,b.price as price,b.publisher as publisher,b.title as title,b.address as address,b.author as author,b.pubdate as pubdate,b.page as page,b.booksize as booksize from bib b , book o where b.bookrecno=o.bookrecno and o.ordertype=1 and o.vendorno='JG' and o.bookrecno not in(select distinct bookrecno from hold)其中 bib 表中有50万记录,book表中有1万条记录,hold表中有300万记录
select b.bookrecno as bookrecno,b.isbn as isbn,b.price as price,b.publisher as publisher,b.title as title,b.address as address,b.author as author,b.pubdate as pubdate,b.page as page,b.booksize as booksize from bib b , book o,hold h where b.bookrecno=o.bookrecno and o.ordertype=1 and o.vendorno='JG' and o.bookrecno
!=h.bookrecno
b.isbn as isbn,
b.price as price,
b.publisher as publisher,
b.title as title,
b.address as address,
b.author as author,
b.pubdate as pubdate,
b.page as page,
b.booksize as booksize
from bib b,
book o,
(select bookrecno
from book
where not in (select distinct bookrecno from hold)) temp
where o.ordertype = 1
and o.vendorno = 'JG'
and o.bookrecno in (temp.bookrecno)
and b.bookrecno = o.bookrecno
from bib b , book o
where not exists(select null from hold h where h.bookrecno =o.bookrecno)
and b.bookrecno=o.bookrecno and o.ordertype=1 and o.vendorno='JG'
说明:1,not exists优于not in
2,筛选量大的条件放在最后
3,如果可以最好对bookrecno加索引