How can I find which elements *in* my set do not match?

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.

EDIT #1: A pure NOT IN i开发者_JAVA百科s 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.

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

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

??

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...

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...

This query could be refined using SELECT DISTINCT or so.

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:

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