SQL query 'NOT IN' vs 'Join'

Question

NOT IN vs using a 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

I have two queries as above, which one is more efficient? Also how can I test the efficiency of queries in sql server?

Answer 1

I would actually recommend not exists for this:

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)
    )

Rationale:

• NOT IN is not null-safe, while NOT EXISTS is

• JOINs usually better fit the use case when you want to return something from the other table - which is not the case here

For performance, you want an index on tbl_b(serviceTypeId, isStandard)