Sql server : How to use "NOT IN" Query in stored procedure instead of left join?

Question

My current code snippet is given below. I want to use "NOT IN" query instead of left join.

    from Complaints C
    inner join Employees E on E.ID = C.EmployeeID 
    left join ComplaintInfractionMapping CIM on CIM.ComplaintID = C.ID

How to use "NOT IN" Query in stored procedure . In below code there is syntax error.

    from Complaints C
    inner join Employees E on E.ID = C.EmployeeID 
    NOT IN SELECT InfractionComment,InfractionDate FROM ComplaintInfractionMapping where ComplaintID =  C.ID

are you sure, are you want to use Not In? because Joins are faster than In / Not In clause — Mihir Shah, Jul 17 '15 at 10:13

score 1 · Answer 1 · answered Jul 17 '15 at 10:15

you can do like this way :-

from    Complaints C
        inner join Employees E on E.ID = C.EmployeeID 
Where   Not Exists (    SELECT   InfractionComment
                                ,InfractionDate 
                        FROM    ComplaintInfractionMapping 
                        where   ComplaintID =  C.ID
                    )

Sql server : How to use "NOT IN" Query in stored procedure instead of left join?

1 Answers1