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

Rentier

Answer 1

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.

Gouge

@Quassnoi, could you please provide the efficient way way writing the same query using either "NOT NULL" and/or "NOT EXISTS" – Rentier

@Elan: your original query is just nice. Just create the indexes on short_code in both tables. – Gouge

@Gouge - You are assuming he doesn't need any information from the domain2 table – Anear

@Aducci: I'm assuming nothing, I'm just rewriting the original query. – Gouge

@Gouge - You say it is less efficient. – Anear

@Aducci: LEFT JOIN is less efficient indeed. The question was "how to rewrite without NOT IN" — here's how. – Gouge

DUDE you are SUPER AWESOME :) Thanks for the above query i was stuck somewhere like hell and You are a SAVIOR :) – Postconsonantal

@VinayChopra: my pleasure, but my query was supposed to be less efficient :) – Gouge

well m not much experienced in this field , so i can't judge .Well i like your attitude , genrally people feels proud after getting knowledge and you are Down to Earth :) . Hmm thanks :) – Postconsonantal

According to this link AVOID to use is null with left join. Better use not in. – Lacroix

@kostasch. That's what's written in the answer – Gouge

@Gouge i am sorry. you are right. I didn't noticed. :) – Lacroix

@Gouge Shouldn't "will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct" be "will work but it is less efficient than a NOT IN (or NOT EXISTS) construct."? – Chemism

Answer 2

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

Anear

Answer 3

I would opt for NOT EXISTS in this case.

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

Fleeta

What difference would NOT EXISTS make? – Gouge

Instead of an outer join or a complex OR operation (with the NOT IN clause) the optimizer should use the equivalent of an exclusion merge join between the two tables. – Fleeta

the optimizer will build exactly same plan for both NOT IN and NOT EXISTS (as long as short_code is not nullable). There will be no "complex OR operations". – Gouge

Didn't realize that. Behavior in Teradata is slightly different. – Fleeta