using NOT IN for multiple tables

Question

How can I simplify 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)

Answer 1

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

Answer 2

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
)