如何查找* my集合中的哪些元素不匹配?

SELECT
*
FROM
users
WHERE
username IN ("john", "bob", "ray", "sexay")

Let's say I have these in my table:

假设我的表格里有这些

ID  USERNAME
------------------
1   john
2   bob
3   jack

I want to know which of my set did not match, so I need "ray" and "sexay". Is there a pure SQL way of doing this? I know I can do this with multiple queries but I have 200 or so users and if it's possible to do it in one query then great.

我想知道哪一组不匹配，所以我需要“ray”和“sexay”。有纯粹的SQL方法吗?我知道我可以用多个查询来完成，但我有大约200个用户，如果可以在一个查询中完成，那就太棒了。

EDIT #1: A pure NOT IN is not sufficient because that would return all users that do not match my username set. I don't need every single one, just every single username string in my given set that doesn't match.

编辑#1:一个纯粹的NOT IN是不够的，因为它会返回所有不匹配我的用户名的用户。我不需要每一个单独的用户名，在我给定的集合中每个用户名字符串都不匹配。

4 个解决方案

#1

You need to restructure your query so that your list of values are in a derived table, at which point you can use one of the standard methods to test which values from one table are not in another. The standard approaches for doing this are:

您需要重构查询，以便您的值列表位于派生表中，这时您可以使用标准方法之一来测试来自一个表的值不在另一个表中。这样做的标准方法是:

NOT EXISTS
不存在
NOT IN
不是在
LEFT JOIN ... WHERE ... IS NULL
离开加入…在那里……为空

Here is a example of NOT EXISTS:

这里有一个不存在的例子:

SELECT T1.username
FROM (
    SELECT 'john' AS username
    UNION ALL
    SELECT 'bob'
    UNION ALL
    SELECT 'ray'
    UNION ALL
    SELECT 'sexay'
) T1
WHERE NOT EXISTS
(
    SELECT NULL
    FROM users
    WHERE T1.username = users.username
)

Or with a join:

或加入:

SELECT T1.username
FROM (
    SELECT 'john' AS username
    UNION ALL
    SELECT 'bob'
    UNION ALL
    SELECT 'ray'
    UNION ALL
    SELECT 'sexay'
) T1
LEFT JOIN users
ON T1.username = users.username
WHERE users.username IS NULL

#2

Select 
    * 
From
    users
Where
    username 
    NOT IN
    (
         Select 
             * 
         from 
             users 
         where 
             username 
         NOT IN 
         ("john", "bob", "ray", "ghost")
    )

? ?

#3

Just a quick hack, not yet tested... And also more than one query and quite MS SQL specific, I think. Not the best solution, but it might be a point to start from...

只是一个简单的破解，还没有测试……我想，还有不止一个查询和SQL MS。这不是最好的解决方案，但它可能是一个起点……

CREATE TABLE #tmp (setToMatch VARCHAR(5))
INSERT INTO #tmp VALUES ('john')
-- and others, of course

SELECT u.name,t.setToMatch FROM USERS AS u
RIGHT OUTER JOIN #tmp AS t
ON #tmp.setToMatch = users.name

When name is NULL, then setToMatch didn't hit...

当名称为空时，则setToMatch没有点击…

This query could be refined using SELECT DISTINCT or so.

可以使用SELECT DISTINCT或so对该查询进行细化。

#4

While not in definitely works in this case, a more efficient (and perhaps more general) way to do this is using a left join with a secondary table (either derived or not). So, to find which usernames in table user do not appear in table ref_table, you can run:

在这种情况下，虽然not in肯定有效，但更有效(也许更通用)的方法是使用左连接和辅助表(派生或不派生)。因此，要查找表用户中哪些用户名没有出现在表ref_table中，可以运行:

select *
  from user
       left join ref_table using (username)
 where ref_table.username is null

#1