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

Question

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

Let's say I have this in my table:

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

I want to know which of my sets didn't match, so I need to "ray" and "sexay". Is there a clean SQL way to do this? I know that I can do this with several requests, but I have 200 or so users, and if it is possible to do this in one request, then excellent.

EDIT # 1: clean is NOT INnot sufficient, because it will return all users that do not match my username. I don’t need each, only every line of the username in my given set that does not match.

Answer 1

, , , . :

• NOT IN
• LEFT JOIN... WHERE... NULL

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
)

:

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

Answer 2

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

??

Answer 3

, ... , , MS SQL. , ...

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

NULL, setToMatch ...

SELECT DISTINCT .

Answer 4

Although it not indefinitely works in this case, a more efficient (and possibly more general) way to do this is to use left joinwith a secondary table (either a derivative or not). So, to find that usernamethe table userdoes not appear in the table ref_table, you can run:

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