MYSQL - 仅当 LEFT JOIN 中的行不存在时才选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15532646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL - Select only if row in LEFT JOIN is not present
提问by Michael Samuel
I have 2 simple mysql tables. The first 1 called mail and has 2 rows:
我有 2 个简单的 mysql 表。第一个 1 称为邮件，有 2 行：
sender | receiver
Marley | Bob
Saget | Bob
The second one called block and has 1 row:
第二个称为块，有 1 行：
blocker | blocked
Bob | Marley
I want to select sender(s) from the first table who sent Bob emails but aren't blocked in the block table. So the results should be:
我想从第一个表中选择发送 Bob 电子邮件但未在阻止表中阻止的发件人。所以结果应该是：
sender
saget
I tried the following query but it's not returning results:
我尝试了以下查询，但没有返回结果：
SELECT * FROM mail
LEFT JOIN block ON (block.blocker = 'Bob')
WHERE (block.blocked <> mail.sender)
回答by Johan
The left join will produce nullrows for the mismatches.
It's those nullrows that you need to filter on.
左连接将为null不匹配生成行。
这null是您需要过滤的那些行。
SELECT * FROM mail
LEFT JOIN block ON (block.blocker = 'Bob')
WHERE block.blocker IS NULL
It's kind of strangle to be joining on a fixed value however, a more common join (given your tables) would be:
连接固定值有点困难，但是更常见的连接（给定您的表）是：
SELECT * FROM mail
LEFT JOIN block ON (block.blocker = mail.receiver
and block.blocked = mail.sender)<<-- these should match
WHERE block.blocker IS NULL <<-- select only mismatches
AND mail.receiver like 'bob';
回答by Lamak
Try this:
尝试这个：
SELECT sender
FROM mail m
WHERE NOT EXISTS (SELECT 1 FROM block
WHERE blocker = m.receiver
AND blocked = m.sender)
