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
)
精彩评论