Help with a WHERE on a LEFT JOIN SQL Query

Question

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 specific document is easy; but I need to generate a query where the results will look like this:

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

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 have been 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.

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?

Answer 1

Move the condition in the WHERE clause to the join condition.

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.

Answer 2

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.

Answer 3

@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):

In the JOIN condition:

AND HasDownloaded.memberID = @memberID

• Client processing time: 4.6
• Total execution time: 35.5
• Wait time on server replies: 30.9

In the WHERE clause:

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

It looks like the WHERE clause is ever-so-slightly more efficient. Interesting! Once again, thanks to both of you for your help.