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

Question

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')
  )

Why doesn't your first query return `cde`? Did you mean to leave that out of the `user` table? — Barmar, Dec 08 '22 at 18:18

score 1 · Answer 1 · answered Dec 08 '22 at 18:27

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

score 1 · Accepted Answer · answered Dec 08 '22 at 18:27

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.

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

2 Answers2