How to write "not in ()" sql query using join

This article:

may be if interest to you.

In a couple of words, this query:

SELECT  d1.short_codeFROM    domain1 d1LEFT JOIN        domain2 d2ON      d2.short_code = d1.short_codeWHERE   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_codeFROM    domain1EXCEPTSELECT  short_codeFROM    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.

sql sql-server tsql notin

SELECT d1.Short_Code FROM domain1 d1LEFT JOIN domain2 d2ON d1.Short_Code = d2.Short_CodeWHERE d2.Short_Code IS NULL

sql sql-server tsql notin

I would opt for NOT EXISTS in this case.

SELECT D1.ShortCodeFROM Domain1 D1WHERE NOT EXISTS    (SELECT 'X'     FROM Domain2 D2     WHERE D2.ShortCode = D1.ShortCode    )

CodeHunter