mysql 与内连接相反

Question

我想使用我的数据库通过电子邮件联系我的用户。我想确保我不会意外地两次联系同一用户。为此，我有一个表格来跟踪谁被联系以及何时被联系。

当我执行 MYSQL 查询时，我想从 email 表中选择电子邮件，并确保 contacted 表中不存在这些条目。

用一句话来表达：如果电子邮件不在 Contacted_Table 中，则从 Email_Table 中选择电子邮件

也许有一种完全不同的方法。我愿意接受所有建议:)谢谢:)

Answer 1

试试这个

SELECT email FROM email_table e 
LEFT JOIN contacted_table c ON e.email = c.email
WHERE c.email IS NULL

Answer 2

select email 
from Email_Table t1 
where not exists (select email 
                  from Contacted_table t2 
                  where t1.email = t2.email)

或

select email 
from Email_Table t1 
where email not in (select email 
                    from Contacted_table)

Answer 3

如果您尝试像这样进行左连接：

SELECT users.email, contacted.email
FROM users LEFT JOIN contacted ON users.email = contacted.email

您将得到类似的结果：

users.email | contacted.email
-----------------------------
[email protected]   | [email protected]
[email protected]   | [email protected]
[email protected]   | [email protected]
[email protected]   | NULL
[email protected]   | NULL

您的目标是获取那些在 contacted 表中没有匹配项的记录，因此您的查询将是：

SELECT users.email
FROM users LEFT JOIN contacted ON users.email = contacted.email
WHERE contacted.email IS NULL

Answer 4

删除电子邮件_表
FROM email_table INNER JOIN 联系
ON email_table.email_id = contacted.email_id;