两个电子邮件地址表，返回一个不在另一个中的地址

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

4 个解决方案

#1

Try:

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

#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.

我已经了解到,当使用更大的数据集时,EXISTS的性能优于IN。

#3

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.

您可以使用join语句执行此操作。基本上,您尝试在其电子邮件地址上加入两个表,并查看连接未能找到匹配项的位置。

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

#4

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

#1