How do I replace this where clause with a join?

Your NOT EXISTS is more efficient in most cases.

LEFT JOIN internally matches all rows then filters to IS NULL. NOT EXISTS doesn't. This row multiplication also happens in all JOIN based code so you may need an extra aggregate (DISTINCT) to fix the output

NOT IN is generally wrong because NULLs cause no match.

You can also use EXCEPT which gives the same plan as NOT EXISTS.

SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID  
FROM @Check CHK
EXCEPT
SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID  
FROM Subscriptions
WHERE ProjectId=@ProjectId           
and NTID=@NTID          

FYI, as per the SQL-92 standard (page 191, Case 3a) the SELECT bit of the EXISTS is ignored.


Yes. You can replace with a LEFT JOIN ... WHERE key IS NULL. Performs much faster.

INSERT INTO Subscriptions(
    ProjectId, RecordTypeCID, NTID,
    Active, Added, LastUpdate, UpdateBy)   
SELECT @ProjectId, RecordTypeCID, @NTID,
    1, GETDATE(), GETDATE(), @NTID  
FROM @Check CHK
LEFT JOIN Subscriptions subs
    ON subs.RecordTypeCID = CHK.RecordTypeCID
        AND NTID = @NTID
        AND ProjectId = @ProjectId
WHERE CHK.ActiveStatus = 1
    AND subs.SubscriptionId IS NULL