How to write “not in ()” sql query using join

This article:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

may be if interest to you.

In a couple of words, this query:

SELECT  d1.short_code
FROM    domain1 d1
LEFT JOIN
        domain2 d2
ON      d2.short_code = d1.short_code
WHERE   d2.short_code IS NULL

will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct.

You can also use this:

SELECT  short_code
FROM    domain1
EXCEPT
SELECT  short_code
FROM    domain2

This is using neither NOT IN nor WHERE (and even no joins!), but this will remove all duplicates on domain1.short_code if any.