Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
                        )  

EDIT: 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
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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
share|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
    
Performance difference is dependent on your environment. Try it both ways and watch for CPU & I/O usage. –  Eric Humphrey - lotsahelp Feb 14 '11 at 16:53
2  
"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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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