如何在值列表中选择不在表中的值？

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

我可以得到其邮件地址在表中的用户如下:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

但是如何选择表中不存在的列表中的值呢？

此外，我怎样才能这样选择:

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

先谢谢你。

156017 次浏览
小开

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

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 JOIN or an EXISTS subquery: 

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)
小开
最佳答案

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)

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
小开

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
小开

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   | a@b.com    | Not exists
Jan  | j@j.nl | j@j.nl     | Exists

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

Good luck :)

小开

This Should work with all SQL versions.

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
小开

Use this : -- SQL Server 2008 or later

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

提交答案