T-SQL:如何在值列表中选择不在表中的值? [英] T-SQL: How to Select Values in Value List that are NOT IN the Table?

问题描述

我有一个电子邮件地址列表，其中一些在我的表格中，有些不在.我想从该列表中选择所有电子邮件，以及它们是否在表格中.

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.

我可以像这样获取邮件地址在表中的用户:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

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')

但是如何在该列表中选择表中不存在的值?

But how can I select values in that list which are not exist in the table?

此外，我该如何选择:

E-Mail | Status
email1 | Exist  
email2 | Exist  
email3 | Not Exist  
email4 | Exist  

提前致谢.

推荐答案

对于 SQL Server 2008

For 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)  

对于以前的版本，您可以使用派生表 UNION ALL 对常量执行类似操作.

For previous versions you can do something similar with a derived table UNION ALL-ing the constants.

/*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

这篇关于T-SQL:如何在值列表中选择不在表中的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！