MySQL:匹配不存在的记录

Question

我有两张桌子：

Customer
+---+-----------+
|ID |VoicemailID|

Voicemail
+---+----------+
|ID |CustomerID|

Voicemail.CustomerID与Customer.ID相关，而visa则相反。

如何从Customer表中选择行，其中Customer.VoicemailID不再是Voicemail表中的有效记录？

在这种情况下，记录过去存在于Voicemail表中，但后来被删除。现在，我需要在Customer表中找到所有具有不存在记录的VoicemailID的记录。

我试过：

SELECT DISTINCT Customer.ID, Customer.VoicemailID
FROM Customers LEFT JOIN Voicemail ON Customer.VoicemailID <> Voicemail.ID

然而，我相信它会返回我想要的结果，与语音邮件实例仍然存在的结果混合在一起。

Answer 1

这应该会给出您想要的结果：

SELECT Customer.ID, Customer.VoicemailID
FROM Customer
   LEFT JOIN Voicemail ON Voicemail.ID = Customer.VoicemailID
WHERE Voicemail.ID IS NULL

Answer 2

你在正确的轨道上与LEFT JOIN。但是，您需要查找匹配项，然后在失败时返回：

SELECT c.ID, c.VoicemailID
FROM Customer c LEFT JOIN
     Voicemail v
     ON c.VoicemailID = v.ID
WHERE v.ID IS NULL;

Answer 3

您可以使用子查询并查找不存在的行。检查WHERE NOT EXISTS在MySQL 文档上的语法

SELECT *
FROM table
WHERE NOT EXISTS(SELECT 1
                 FROM otherTable 
                 WHERE table.id = otherTable.someField )