mysql - get list of usernameNOT IN / NOT EXIST the database

I have a list of usernames

username
abc
xyz
cde

select username from users where username in ('abc','xyz','cde')

return abc and xyz

How can i get usernames from my list , but not in the database using sql
which will be cde in this case

i tried this , may be am close, not sure

SELECT username
FROM (
        VALUES 
        ROW(‘abc'),
        ROW(‘xyz'),
        ROW(‘cde') ,
)  as usernames (username)
WHERE NOT exists (
  SELECT username  FROM user where username in (‘abc’,’xyz’,’cde')
  )

CodePudding user response：

The subquery needs to be correlated with the main query.

SELECT username
FROM (
        VALUES 
        ROW('abc'),
        ROW('xyz'),
        ROW('cde')
) as usernames (username)
WHERE NOT exists (
    SELECT username  
    FROM user u 
    where u.username = usernames.username
)

DEMO

See Return row only if value doesn't exist for other ways to return rows that exist in the synthesized table but not in the user table.

CodePudding user response：

You could join table with your list and detect where there is no joined value. I assume that users is existing table. Be also careful, your code in question uses various types of apostrophes and you named your list the same as existing table. You also typed in additional comma. I don't have MySql environ. readily available, so just let me know if it will work.

SELECT my_usernames.username
FROM (
    VALUES 
    ROW('abc'),
    ROW('xyz'),
    ROW('cde')
 )  as my_usernames (username)
LEFT JOIN users u ON
   u.username = my_usernames.username
WHERE my_usernames.username IS NULL --=related record not fund