SQL 如何使用join编写“not in()”sql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5676559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write "not in ()" sql query using join
提问by manu
Could some one please provide how to write following sql query using joins. I do not want use not inas well as if possible I would like to replace wherecondition as well.
有人可以提供如何使用连接编写以下 sql 查询。我不想尽可能好地使用not in我也想替换where条件。
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
我正在使用 SQL Server 2008
回答by Quassnoi
This article:
本文：
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.
会起作用，但它的效率不如 a NOT NULL(or NOT EXISTS) 构造。
You can also use this:
你也可以使用这个：
SELECT short_code
FROM domain1
EXCEPT
SELECT short_code
FROM domain2
This is using neither NOT INnor WHERE(and even no joins!), but this will remove all duplicates on domain1.short_codeif any.
这既不使用NOT IN也不使用WHERE（甚至不使用连接！），但这将删除所有重复项（domain1.short_code如果有）。
回答by Aducci
SELECT d1.Short_Code
FROM domain1 d1
LEFT JOIN domain2 d2
ON d1.Short_Code = d2.Short_Code
WHERE d2.Short_Code IS NULL
回答by Rob Paller
I would opt for NOT EXISTSin this case.
NOT EXISTS在这种情况下，我会选择。
SELECT D1.ShortCode
FROM Domain1 D1
WHERE NOT EXISTS
(SELECT 'X'
FROM Domain2 D2
WHERE D2.ShortCode = D1.ShortCode
)
