T-SQL：如何在值列表中选择不在表中的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9113204/
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
T-SQL: How to Select Values in Value List that are NOT IN the Table?
提问by kubilay
I have a list of e-mail addresses, some of them are in my table, some of them are not. I want to select all e-mails from that list and whether they are in the table or not.
我有一个电子邮件地址列表，其中一些在我的表格中，有些不在。我想从该列表中选择所有电子邮件，以及它们是否在表格中。
I can get users whose mail adresses are in the table like this:SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')
我可以像这样获取邮件地址在表中的用户：SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')
But how can I select values in that list which are not exist in the table?
但是如何在该列表中选择表中不存在的值？
Moreover, how can I select like this:
此外，我该如何选择：
E-Mail | Status
email1 | Exist
email2 | Exist
email3 | Not Exist
email4 | Exist
Thanks in advance.
提前致谢。
回答by Martin Smith
For SQL Server 2008
对于 SQL Server 2008
SELECT email,
CASE
WHEN EXISTS(SELECT *
FROM Users U
WHERE E.email = U.email) THEN 'Exist'
ELSE 'Not Exist'
END AS [Status]
FROM (VALUES('email1'),
('email2'),
('email3'),
('email4')) E(email)
For previous versions you can do something similar with a derived table UNION ALL-ing the constants.
对于以前的版本，您可以对派生表执行类似的操作 -UNION ALL对常量进行处理。
/*The SELECT list is the same as previously*/
FROM (
SELECT 'email1' UNION ALL
SELECT 'email2' UNION ALL
SELECT 'email3' UNION ALL
SELECT 'email4'
) E(email)
Or if you want just the non-existing ones (as implied by the title) rather than the exact resultset given in the question, you can simply do this
或者，如果您只想要不存在的（如标题所暗示的）而不是问题中给出的确切结果集，您可以简单地执行此操作
SELECT email
FROM (VALUES('email1'),
('email2'),
('email3'),
('email4')) E(email)
EXCEPT
SELECT email
FROM Users
回答by ypercube??
You need to somehow create a table with these values and then use NOT IN. This can be done with a temporary table, a CTE (Common Table Expression) or a Table Values Constructor(available in SQL-Server 2008):
您需要以某种方式使用这些值创建一个表，然后使用NOT IN. 这可以通过临时表、CTE（通用表表达式）或表值构造函数（在 SQL-Server 2008 中可用）来完成：
SELECT email
FROM
( VALUES
('email1')
, ('email2')
, ('email3')
) AS Checking (email)
WHERE email NOT IN
( SELECT email
FROM Users
)
The second result can be found with a LEFT JOINor an EXISTSsubquery:
可以使用 aLEFT JOIN或EXISTS子查询找到第二个结果：
SELECT email
, CASE WHEN EXISTS ( SELECT *
FROM Users u
WHERE u.email = Checking.email
)
THEN 'Exists'
ELSE 'Not exists'
END AS status
FROM
( VALUES
('email1')
, ('email2')
, ('email3')
) AS Checking (email)
回答by Lamak
You should have a table with the list of emails to check. Then do this query:
您应该有一个表格，其中包含要检查的电子邮件列表。然后做这个查询：
SELECT E.Email, CASE WHEN U.Email IS NULL THEN 'Not Exists' ELSE 'Exists' END Status
FROM EmailsToCheck E
LEFT JOIN (SELECT DISTINCT Email FROM Users) U
ON E.Email = U.Email
回答by Mark Kremers
When you do not want to have the emails in the list that are in the database you'll can do the following:
如果您不想在列表中包含数据库中的电子邮件，您可以执行以下操作：
select u.name
, u.EMAIL
, a.emailadres
, case when a.emailadres is null then 'Not exists'
else 'Exists'
end as 'Existence'
from users u
left join ( select 'email1' as emailadres
union all select 'email2'
union all select 'email3') a
on a.emailadres = u.EMAIL)
this way you'll get a result like
这样你会得到这样的结果
name | email | emailadres | existence
-----|--------|------------|----------
NULL | NULL | [email protected] | Not exists
Jan | [email protected] | [email protected] | Exists
Using the IN or EXISTS operators are more heavy then the left join in this case.
在这种情况下，使用 IN 或 EXISTS 运算符比左连接更重。
Good luck :)
祝你好运 ：）
回答by Anju313
This Should work with all SQL versions.
这应该适用于所有 SQL 版本。
SELECT E.AccessCode ,
CASE WHEN C.AccessCode IS NOT NULL THEN 'Exist'
ELSE 'Not Exist'
END AS [Status]
FROM ( SELECT '60552' AS AccessCode
UNION ALL
SELECT '80630'
UNION ALL
SELECT '1611'
UNION ALL
SELECT '0000'
) AS E
LEFT OUTER JOIN dbo.Credentials C ON E.AccessCode = c.AccessCode
回答by Ardalan Shahgholi
Use this : -- SQL Server 2008 or later
使用：-- SQL Server 2008 或更高版本
SELECT U.*
FROM USERS AS U
Inner Join (
SELECT
EMail, [Status]
FROM
(
Values
('email1', 'Exist'),
('email2', 'Exist'),
('email3', 'Not Exist'),
('email4', 'Exist')
)AS TempTableName (EMail, [Status])
Where TempTableName.EMail IN ('email1','email2','email3')
) As TMP ON U.EMail = TMP.EMail
