否定选择SQL查询

我的使命是从我们的sql数据库中返回一个否认的选择.我会尽也许地界说尺度.到今朝为止,我还没有建造出有用的查询.

营业表

[公交车名称] [公交车ID]

勾当表

[勾当ID] [公交车ID]

勾当扩展表

[分机ID] [勾当ID] [巴士ID]

我必要在关联表中没有该企业ID#记录的全部企业的企业名称.简朴地说,全部企业都没有勾当.营业ID可以存在于一个或两个关联表中.

在实行行使毗连举办查询而不是在语句中存在或不存在时,这会导致我几个小时的贫困.没有乐成.

有任何设法吗？

办理要领

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.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!