Two tables of emails addresses, return where one is not in the other

Question

I have a list of e-mails from one database which I want to check against a list of unsubscribed e-mails from another database. If they exist in both lists, then I don't want them returned.

SELECT distinct `payer_email` as `email` 
FROM `database1`.`paypal_table`
WHERE `payer_email` != 
(SELECT `email` 
 FROM `database2`.`Unsubscribers` 
 WHERE `email` 
 LIKE `database1`.`paypal_table`.`payer_email`)

Answer 1

Try:

`payer_email` NOT IN (SELECT `email` FROM `database2`.`Unsubscribers`)

Answer 2

I would use:

WHERE NOT EXISTS (SELECT.....)

I have come to learn that EXISTS is better performing than IN when using larger data sets.

Answer 3

Try this-

select distinct(email) from table 1 where email not in (select distinct(email) from table 2)
union
select distinct(email) from table 2 where email not in (select distinct(email) from table 1)

cheers

Answer 4

You can do this with a join statement. Basically you try to join the two tables on their email addresses, and look at where the join failed to find a match.

SELECT DISTINCT email FROM table1
       LEFT JOIN table2 ON table1.email == table2.email
       WHERE table2.email IS NULL