8

Typically when I see SQL that uses something like:

select * from employees where epmloyeeTypeId in (select id from type where name = 'emp') 

I replace the where with this:

select e.* from employees e 
inner join type t on t.id=e.epmloyeeTypeId and t.name = 'emp'

Is it possible to do the same with the inverse in case its is a not in (like below) instead of an in clause?

INSERT into Subscriptions(ProjectId, RecordTypeCID, NTID, Active, Added, LastUpdate, UpdateBy)   
 SELECT @ProjectId, RecordTypeCID, @NTID, 1, GETDATE(), GETDATE(), @NTID  
 FROM @Check CHK  
 WHERE CHK.ActiveStatus=1  
        And Not Exists (SELECT SubscriptionId FROM Subscriptions  
                        WHERE ProjectId=@ProjectId           
                        and NTID=@NTID          
                        and RecordTypeCID = CHK.RecordTypeCID
                        )  

Additional considerations

Can I do this:

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

        AND CHK.ActiveStatus = 1
        AND subs.SubscriptionId IS NULL
6

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
|improve this answer|||||
  • what the performance different between the two? I would think joins are way faster than using in(...) or not in(...), never thought about exists(...) any idea? – kacalapy Feb 14 '11 at 16:46
  • also can i include every thing in the join, see my edit at the bottom of the question – kacalapy Feb 14 '11 at 16:49
  • 1
    You have to have the WHERE clause as I stated. – Eric Humphrey - lotsahelp Feb 14 '11 at 16:52
  • 4
    "Performs much faster"? Than NOT EXISTS or NOT IN? If NOT IN, yes. If NOT EXISTS, pretty equal. – gbn Feb 14 '11 at 18:54
  • 1
    Example or on SO – gbn Feb 14 '11 at 19:00
7

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.