LEFT JOIN SQL 查询中的 WHERE 帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/219046/
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
Help with a WHERE on a LEFT JOIN SQL Query
提问by ine
I'm trying to construct a query that will include a column indicating whether or not a user has downloaded a document. I have a table called HasDownloaded with the following columns: id, documentID, memberID. Finding out whether a user has downloaded a specificdocument is easy; but I need to generate a query where the results will look like this:
我正在尝试构建一个查询，其中将包含一个列，指示用户是否已下载文档。我有一个名为 HasDownloaded 的表，其中包含以下列：id、documentID、memberID。找出用户是否下载了特定文档很容易；但我需要生成一个查询，结果将如下所示：
name id
----------------------
abc NULL
bbb 2
ccc 53
ddd NULL
eee 13
The ID isn't really important; what I'm interested in is whether the document has been downloaded (is it NULL or not).
ID 并不重要。我感兴趣的是文档是否已下载（是否为 NULL）。
Here is my query:
这是我的查询：
SELECT Documents.name, HasDownloaded.id FROM Documents
LEFT JOIN HasDownloaded ON HasDownloaded.documentID = Documents.id
WHERE HasDownloaded.memberID = @memberID
The problem is, this will only return values if an entry exists for the specified user in the HasDownloaded table. I'd like to keep this simple and only have entries in HasDownloaded for documents that havebeen downloaded. So if user 1 has downloaded abc, bbb, and ccc, I still want ddd and eee to show up in the resulting table, just with the id as NULL. But the WHERE clause only gives me values for which entries exists.
问题是，如果 HasDownloaded 表中存在指定用户的条目，这只会返回值。我想保持这个简单，并且在 HasDownloaded 中只有已下载文档的条目。因此，如果用户 1 已经下载了 abc、bbb 和 ccc，我仍然希望 ddd 和 eee 显示在结果表中，只是将 id 设为 NULL。但是 WHERE 子句只给我存在条目的值。
I'm not much of a SQL expert - is there an operator that will give me what I want here? Should I be taking a different approach? Or is this impossible?
我不是一个 SQL 专家 - 有没有一个运算符可以给我我想要的东西？我应该采取不同的方法吗？或者这是不可能的？
回答by matt b
Move the condition in the WHERE clause to the join condition.
将 WHERE 子句中的条件移至连接条件。
SELECT Documents.name, HasDownloaded.id FROM Documents
LEFT JOIN HasDownloaded ON HasDownloaded.documentID = Documents.id
AND HasDownloaded.memberID = @memberID
This is necessary whenever you want to refer to a left join-ed table in what would otherwise be the WHERE clause.
每当您想在 WHERE 子句中引用左连接表时，这是必要的。
回答by Mark Brackett
WHERE HasDownloaded.memberId IS NULL OR HasDownloaded.memberId = @memberId
would be the normal way to do that. Some would shorten it to:
将是做到这一点的正常方式。有些人会将其缩短为：
WHERE COALESCE(HasDownloaded.memberId, @memberId) = @memberId
You can, as Matt B. shows, do it in your JOIN condition - but I think that's much more likely to confuse folks. If you don't understand WHY moving it to the JOIN clause works, then I'd strongly suggest staying away from it.
正如 Matt B. 所示，您可以在 JOIN 条件下执行此操作 - 但我认为这更有可能让人们感到困惑。如果您不明白为什么将其移至 JOIN 子句有效，那么我强烈建议您远离它。
回答by ine
@Mark: I understand why the JOIN syntax works, but thanks for the warning. I do think your suggestion is more intuitive. I was curious to see which was more efficient. So I ran a quick test (this was rather simplistic, I'm afraid, over only 14 rows and 10 trials):
@Mark：我理解为什么 JOIN 语法有效，但感谢您的警告。我确实认为您的建议更直观。我很好奇哪个更有效。所以我进行了一个快速测试（这很简单，恐怕只有 14 行和 10 次试验）：
In the JOIN condition:
在 JOIN 条件下：
AND HasDownloaded.memberID = @memberID
- Client processing time: 4.6
- Total execution time: 35.5
- Wait time on server replies: 30.9
- 客户端处理时间：4.6
- 总执行时间：35.5
- 服务器回复的等待时间：30.9
In the WHERE clause:
在 WHERE 子句中：
WHERE HasDownloaded.memberId IS NULL OR HasDownloaded.memberId = @memberId
- Client processing time: 7.7
- Total execution time: 27.7
- Wait time on server replies: 22.0
- 客户端处理时间：7.7
- 总执行时间：27.7
- 服务器回复的等待时间：22.0
It looks like the WHERE clause is ever-so-slightly more efficient. Interesting! Once again, thanks to both of you for your help.
看起来 WHERE 子句效率更高。有趣的！再次感谢两位的帮助。
