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

Question:

Could some one please provide how to write following sql query using joins. I do not want use not in as well as if possible I would like to replace where condition as well.

SELECT d1.Short_Code

FROM domain1 d1

WHERE d1.Short_Code NOT IN (

  SELECT d2.Short_Code

  FROM Domain2 d2

)

I am using SQL Server 2008

Answer

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

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