开发者

using NOT IN for multiple tables

How can I simpl开发者_运维百科ify multiple 'not in' queries? Is it efficient to use multiple subqueries: Not in (...) and Not in (..) and Not in (..)

I am using count (sorry forgot about that)

 Select count (VisitorID)

 from Company

 where VisitorID not in (select VisitorID from UserLog where ActionID = 2 )

 and VisitorID not in (select VisitorID from Supplies where productID = 4)


Select count (VisitorID)

from Company C
where
NOT EXISTS (select * from UserLog U where ActionID = 2 AND C.VisitorID  = U.VisitorID)
AND
NOT EXISTS (select * from Supplies S where productID = 4 AND S.VisitorID  = U.VisitorID)

Why NOT EXISTS?

  • NOT IN: Any NULL VisitorID values in UserLog or Supplies means no match

  • (LEFT JOIN): multiple output rows if many UserLog or Supplies per VisitorID. Needs DISTINCT which changes the plan

Generally, NOT EXISTS is the only correct option


You could use a UNION for the id group

Select User

from Company

where VisitorID not in (
select VisitorID from UserLog where ActionID = 2 
UNION
select VisitorID from Supplies where productID = 4
)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜