T-SQL：如何选择值列表中不在表中的值？

Question

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

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

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

此外，如何进行这样的选择：

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

- kubilay

---

你是指微软SQL Server中的T-SQL吗？如果是，那么是哪个版本？ - user743382

是的，我使用的是MS SQL Server。我同时使用2005和2008 R2版本。 - kubilay

好的，既然这样，我建议采用Martin Smith的答案。我之所以问是因为SQL Server 2000仍然被广泛使用，而他的答案在那个版本上不起作用。 - user743382

而且通过他的修改，他的答案甚至可以适用于所有版本 :) - user743382

感谢hvd的提问，如果你没有问，Martin可能不会进行编辑。我不知道SQL可以执行远程服务器的命令，我的Studio是2008版，但远程服务器是2005版，所以(VALUES)无法使用，但(UNION ALL)可以。 - kubilay

Answer 1

针对 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。

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

如果您只想获取不存在的结果（如标题所示），而不是问题中给出的精确结果集，那么您可以简单地执行此操作

SELECT email
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  
EXCEPT
SELECT email
FROM Users

- Martin Smith

Answer 2

你需要以某种方式创建一个包含这些值的表，然后使用NOT IN。这可以通过临时表、CTE（公共表达式）或表值构造函数（在 SQL-Server 2008 中可用）来完成：

SELECT email
FROM
    ( VALUES 
        ('email1')
      , ('email2')
      , ('email3')
    ) AS Checking (email)
WHERE email NOT IN 
      ( SELECT email 
        FROM Users
      ) 

第二个结果可以通过使用 LEFT 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)

- ypercubeᵀᴹ

---

这将给出表中不在列表中的值。问题要求相反的情况：列表中不在表中的值。 - user743382

即使进行编辑，如果Users.email可以为空（而我们不知道它是否为空），仍然无法正确。 - user743382

@hvd：你说得对，我们不知道它是否可为空。但是查询仍然会正确回答。 - ypercubeᵀᴹ

你的第二个查询会返回结果，而第一个不会。当使用WHERE 'a' IN ('b', NULL, 'c')时，结果会是UNKNOWN而不是FALSE，并且NOT UNKNOWN仍然是UNKNOWN。换句话说，如果任何用户的电子邮件地址为NULL，第一个查询将不会返回任何内容。 - user743382

@hvd：我知道，但这可能是OP想要的“正确”行为 :) 毕竟他的列中为什么会有Nulls呢？ - ypercubeᵀᴹ

是的，从技术上讲，当用户的电子邮件地址未知时，该用户的电子邮件地址恰好是OP硬编码列表中少数几个电子邮件地址之一，并且OP希望考虑到它是这些少数电子邮件地址之一的可能性。我认为这种可能性很低，可以忽略不计。 - user743382

Answer 3

您应该拥有一张包含待检查电子邮件列表的表格。然后执行以下查询：

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

- Lamak

---

谢谢，我不想走冗长的路。Martin 的回答解决了问题。 - kubilay

Answer 4

当你不想要数据库中已经存在的电子邮件出现在列表中时，可以执行以下操作：

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)

这样你就可以得到类似的结果

name | email  | emailadres | existence
-----|--------|------------|----------
NULL | NULL   | [e-mail]    | Not exists
Jan  | [e-mail] | [e-mail]     | Exists

在这种情况下，使用IN或EXISTS运算符比左连接更加繁重。

祝好运 :)

- Mark Kremers

---

谢谢，你的回答虽然晚了但是也是正确的。我很高兴不用为值创建一个表格。 - kubilay

EXISTS会比外连接更有效，因为它能够进行短路运算。如果在 Users 中存在重复，则您的方法可能会多次返回相同的地址。请参阅CASE表达式中的子查询以了解 EXISTS 如何处理的一些详细信息。 - Martin Smith

Answer 5

使用此版本： -- 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

- Ardalan Shahgholi

Answer 6

这应该适用于所有的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

- VoidA313