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