时间戳

首页 > 解决方案 > SQL 查询“未加入”与“加入”

sql - SQL 查询“未加入”与“加入”

问题描述

NOT IN 与使用 JOIN

SELECT  COUNT(*)
FROM TBL_A AS A
WHERE A.MailDatId = 185208
AND SUBSTRING(A.PIMBRecord,3,3) NOT IN (SELECT B.ServiceTypeId FROM TBL_B AS B WHERE B.IsStandard = 1);   

 SELECT COUNT(*) FROM TBL_A AS A 
    LEFT JOIN (SELECT B.ServiceTypeId FROM TBL_B AS B WHERE B.IsStandard = 1) C
    ON SUBSTRING(A.PIMBRecord,3,3) = C.ServiceTypeId
    WHERE A.MailDatId = 185208
    and C.ServiceTypeId IS NULL

上面我有两个查询，哪一个更高效？另外如何测试sql server中查询的效率？

标签： sqlsql-servertsqljoinsql-in

解决方案

我实际上会推荐not exists这个：

select count(*)
from tbl_a a
where
    a.mailDatId = 
    and not exists (
        select 1 
        from tbl_b as b 
        where b.isStandard = 1 and b.serviceTypeId = substring(a.PIMBRecord,3,3)
    )

理由：

  • NOT IN不是空安全的，NOT EXISTS而是

  • JOIN当您想从另一个表返回某些东西时，s 通常更适合用例 - 这里不是这种情况

为了性能，您需要一个索引tbl_b(serviceTypeId, isStandard)

推荐阅读