我的任务是从我们的sql数据库中返回一个否定的选择.我会尽可能地定义标准.到目前为止,我还没有制作出有效的查询.
业务表
[公交车名称] [公交车ID]
活动表
[活动ID] [公交车ID]
活动扩展表
[分机ID] [活动ID] [巴士ID]
我需要在关联表中没有该企业ID#记录的所有企业的企业名称.简单地说,所有企业都没有活动.业务ID可以存在于一个或两个关联表中.
在尝试使用连接进行查询而不是在语句中存在或不存在时,这会导致我几个小时的麻烦.没有成功.
有任何想法吗？
解决方法
使用NOT IN
SELECT b.*
FROM BUSINESS b
WHERE b.business_id NOT IN (SELECT a.business_id
FROM ACTIVITY a)
AND b.business_id NOT IN (SELECT ae.business_id
FROM ACTIVITY_EXTENSION ae)
使用NOT EXISTS
SELECT b.*
FROM BUSINESS b
WHERE NOT EXISTS (SELECT NULL
FROM ACTIVITY a
WHERE a.business_id = b.business_id)
AND NOT EXISTS (SELECT NULL
FROM ACTIVITY_EXTENSION ae
WHERE ae.business_id = b.business_id)
使用LEFT JOIN / IS NULL
SELECT b.*
FROM BUSINESS b
LEFT JOIN ACTIVITY a ON a.business_id = b.business_id
LEFT JOIN ACTIVITY_EXTENSION ae ON ae.business_id = b.business_id
WHERE a.business_id IS NULL
AND ae.business_id IS NULL
结论
因为关系是外键(business_id),所以假设它们都不为null是安全的.在这种情况下,NOT IN和NOT EXISTS是在sql Server中查找缺失值的最佳方法. LEFT JOIN / IS NULL效率较低 – you can read more about it in this article.
