Question 21842 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search