Query MySQL database find missing email addresses-CodePudding

I am trying to extract email addresses from given list, that not persists in MySql database. My query:

SELECT * 
FROM users 
WHERE `user_email` IN ('[email protected]', '[email protected]', '[email protected]')

First two email addresses are in database, but the last one is not. My target is to print only emails that are NOT in database. How is that possible?

CodePudding user response：

SELECT * 
FROM (SELECT '[email protected]' user_email
      UNION ALL
      SELECT '[email protected]'
      UNION ALL
      SELECT '[email protected]') emails_to_check_for
LEFT JOIN users USING (user_email)
WHERE users.user_email IS NULL;

CodePudding user response：

create a temporary table and insert all the email ids that you want to check

create table check_emailid(email_id varchar(255))

insert into check_emailid(email_id )
values('[email protected]') 
values('[email protected]')
values('[email protected]')

select * 
from check_emailid 
where email_id not in (SELECT user_email
                       FROM users)