blocks|key|446400|text|您需要以某种方式使用这些值创建一个表，然后使用NOT+IN。这可以通过临时表、常用表表达式或Table+Values+Constructor+(在SQL-Server2008中可用)来完成：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|446401|SELECT+email
FROM
++++(+VALUES+
++++++++('email1')
++++++,+('email2')
++++++,+('email3')
++++)+AS+Checking+(email)
WHERE+email+NOT+IN+
++++++(+SELECT+email+
++++++++FROM+Users
++++++)+|code-block|syntax|javascript|446402|第二个结果可以通过LEFT+JOIN或EXISTS子查询找到：|446403|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)|446404|entityMap|0|LINK|mutability|MUTABLE|url|http://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/^0|N|6|1A|O|0|0|0|9|9|J|6|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|18|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

You need to somehow create a table with these values and then use <code>NOT IN</code>. This can be done with a temporary table, a CTE (Common Table Expression) or a <a href="http://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/" rel="noreferrer">Table Values Constructor</a> (available in SQL-Server 2008): 

<pre><code>SELECT email
FROM
 ( VALUES 
 ('email1')
 , ('email2')
 , ('email3')
 ) AS Checking (email)
WHERE email NOT IN 
 ( SELECT email 
 FROM Users
 ) 
</code></pre>

The second result can be found with a <code>LEFT JOIN</code> or an <code>EXISTS</code> subquery: 

<pre><code>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)
</code></pre>

blocks|key|446453|text|对于SQL+Server+2008|type|unstyled|depth|inlineStyleRanges|entityRanges|data|446454|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)++|code-block|syntax|javascript|446455|对于以前的版本，您可以使用派生表UNION+ALL-ing常量来执行类似的操作。|offset|length|style|CODE|446456|/*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)|446457|或者，如果您只想要不存在的结果集(正如标题所暗示的那样)，而不是问题中给出的确切结果集，您可以简单地这样做|446458|SELECT+email
FROM+++(VALUES('email1'),
++++++++++++++('email2'),
++++++++++++++('email3'),
++++++++++++++('email4'))+E(email)++
EXCEPT
SELECT+email
FROM+Users|446459|entityMap^0|0|0|G|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

For SQL Server 2008

<pre><code>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) 
</code></pre>

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

<pre><code>/*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)
</code></pre>

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 

<pre><code>SELECT email
FROM (VALUES('email1'),
 ('email2'),
 ('email3'),
 ('email4')) E(email) 
EXCEPT
SELECT email
FROM Users
</code></pre>

blocks|key|446481|text|您应该有一个包含要检查的电子邮件列表的表。然后执行以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|446482|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|code-block|syntax|javascript|446483|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You should have a table with the list of emails to check. Then do this query:

<pre><code>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
</code></pre>

blocks|key|660946|text|如果您不希望数据库中的电子邮件列表中包含这些电子邮件，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|660947|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)|code-block|syntax|javascript|660948|这样你就会得到如下的结果|660949|name+%7C+email++%7C+emailadres+%7C+existence
-----%7C--------%7C------------%7C----------
NULL+%7C+NULL+++%7C+a@b.com++++%7C+Not+exists
Jan++%7C+j@j.nl+%7C+j@j.nl+++++%7C+Exists|660950|在这种情况下，使用IN或EXISTS运算符比使用left+join更繁重。|660951|祝你好运:)|660952|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

When you do not want to have the emails in the list that are in the database you'll can do the following:

<pre><code>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)
</code></pre>

this way you'll get a result like

<pre><code>name | email | emailadres | existence
-----|--------|------------|----------
NULL | NULL | a@b.com | Not exists
Jan | j@j.nl | j@j.nl | Exists
</code></pre>

Using the IN or EXISTS operators are more heavy then the left join in this case.

Good luck :)

blocks|key|660983|text|这应该适用于所有SQL版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|660984|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|code-block|syntax|javascript|660985|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This Should work with all SQL versions.

<pre><code>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
</code></pre>

blocks|key|661039|text|使用以下命令：--+SQL+Server+2008或更高版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|661040|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|code-block|syntax|javascript|661041|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use this : 
-- SQL Server 2008 or later

<pre><code>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
</code></pre>

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: 
<code>SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')</code>

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

Moreover, how can I select like this: 

<pre><code>E-Mail | Status
email1 | Exist 
email2 | Exist 
email3 | Not Exist 
email4 | Exist 
</code></pre>

Thanks in advance.

T-SQL: How to Select Values in Value List that are NOT IN the Table?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个电子邮件地址列表，有些在我的表中，有些不在。我想从该列表中选择所有电子邮件，而不管它们是否在表中。我可以获得邮件地址在表中的用户，如下所示：SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')但是如何选择列表中不存在于表中...

问T-SQL:如何选择值列表中不在表中的值？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL:如何选择值列表中不在表中的值？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL:如何选择值列表中不在表中的值？
EN