sql - 对多个表使用 NOT IN

Question

如何简化多个“不在”查询？使用多个子查询是否有效:Not in (...) and Not in (..) and Not in (..)

我正在使用计数(抱歉忘记了)

 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)

为什么不存在？

NOT IN:UserLog 或 Supplies 中的任何 NULL VisitorID 值表示不匹配

(LEFT JOIN):如果每个访问者 ID 有多个 UserLog 或 Supplies，则输出多个行。需要改变计划的 DISTINCT

通常，NOT EXISTS 是唯一正确的选项