Convert Sub-Query with "not in" operator to join

Question

Is it possible to convert a sub-query with NOT IN operator to join? I need this query using joins

SELECT *
FROM Contacts
WHERE ContactIntID NOT IN(
    SELECT LinkToIntID
    FROM Requirements
    WHERE Requirements.uuJobStatus = 'Open')

Answer 1

Yes.

SELECT c.*
FROM Contacts c
LEFT JOIN 
  (SELECT LinkToIntID FROM Requirements WHERE uuJobStatus='Open') r 
  ON (c.ContactIntID=r.LinkToIntID)
WHERE r.LinkToIntID IS NULL

and simplified:

SELECT c.*
FROM Contacts c
LEFT JOIN 
  Requirements r 
  ON  r.uuJobStatus = 'Open'
  AND c.ContactIntID = r.LinkToIntID
WHERE r.LinkToIntID IS NULL

Answer 2

Using non exists will generally perform better than using LEFT JOIN. With the above answers, the contacts will join all records regardless whether they match or not, and then filter them out later using the IS NULL clause.

SELECT *
FROM Contacts c
WHERE NOT EXISTS
(
    SELECT 1
    FROM Requirements r
    WHERE Requirements.uuJobStatus = 'Open'
    AND ContractIntID = r.LinkToIntID
)

Edit: Realised you wanted a non sub query, but nevertheless worth noting my reply :)