How to write "not in ()" sql query using join
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
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.
SELECT d1.Short_Code
FROM domain1 d1
LEFT JOIN domain2 d2
ON d1.Short_Code = d2.Short_Code
WHERE d2.Short_Code IS NULL