日期:2014-05-16 浏览次数:20376 次
IN 和EXISTS
IN 是把外表和内表作hash连接,而EXISTS是对外表作loop循环,每次loop循环再对内表进行查询。EXISTS比in效率高的说法是不正确的。如果查询的两个表大小相当,那么用in和EXISTS差别不大。
如果连个表中一个较大,一个是小表,则子查询表达的用EXISTS,子查询表小的用in。
例如:表Athor(小表),表Winner(大表)
SELECT * FROM Athor AthorCode in (select AthorCode? from Winner)
效率低,用到了Athor的索引
?
SELECT * FROM Athor EXISTS(select AthorCode? from Winner WHERE Athor.AthorCODE=Winner.AthorCODE)
效率高,用到了Winner 的索引
not in 和not exists
如果查询语句使用了not in 那么内外表都进行了全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in 要快。
NOT IN
select AthorCode,AthorName
from Athor
where AthorCode not in (
select AthorCode
from Winner)
NOT EXISTS
select AthorCode,AthorName
from Athor AS A
where? NOT EXISTS(
select AthorCode
from Winner? AS B
where A.AthorCode=B.AthorCode)
LEFT JOIN
select A.AthorCode,AthorName
from Athor AS A Left join Winner B
ON A.AthorCode=B.AthorCode
WHERE B.WinnerDate IS NOT NULL