MySQL JOINs with NULL values

I'm trying to do a rather complex (for me, at least) query that involves fetching rows that may have NULL values.

There are four tables here, tags, questions_tags, users_experience, and answers. The connections are rather straightforward. Questions are tagged, tags have names, users give answers to questions, and users have experience with particular tags. I want to find answers that users have given, and their experience (which may be NULL) for that questions' tags. I'm ordering by the number of answers given for a particular tag.

My query is as follows. This is not at all optimized (and if you have optimization suggestions, please suggest away!):

SELECT t.tag_id, t.name, ue.body, COUNT(a.qid)
FROM tags AS t
LEFT JOIN users_experience AS ue
  ON t开发者_开发技巧.tag_id = ue.tag_id
LEFT JOIN questions_tags AS qt
  ON qt.tag_id = t.tag_id
LEFT JOIN answers AS a
  ON a.qid = qt.qid
WHERE a.uid=1
GROUP BY t.tag_id
ORDER BY COUNT(a.qid) DESC;

The problem I'm facing with the above query is that if anyone has noted experience for a particular tag, that will show up for the user whether it is their experience or not. I'd like to see only that particular user's experience, which this query is just not doing. I've run into this problem elsewhere and been stumped, so have had to hack around it.

I tried adding AND ue.uid = 1 to the query, but that will limit the results on only those where the experience has already been given, and not return the NULL values I desire, as well.

Any thoughts on what to do?

---

Your AND ue.uid = 1 instinct wasn't wrong, but it belongs in the ON clause so it's part of the JOIN, and it's used to determine which rows are eligible to join.

LEFT JOIN users_experience AS ue
  ON t.tag_id = ue.tag_id AND ue.uid = a.uid

(And that join should then be placed under the answers join.)

---

I think you need to include a user's table. Filter by the uid in the user's table (where it's guaranteed to be) and that should give you what you want. (including the null values). I don't know exactly what your user table looks like but I'm assuming your query would be something like this:

SELECT t.tag_id, t.name, ue.body, COUNT(a.qid)
     FROM users
LEFT JOIN users_experience AS ue
  ON users.uid = ue.uid
LEFT JOIN tags 
  ON t.tag_id = ue.tag_id
LEFT JOIN questions_tags AS qt
  ON qt.tag_id = t.tag_id
LEFT JOIN answers AS a
  ON a.qid = qt.qid
WHERE users.uid=1
GROUP BY t.tag_id
ORDER BY COUNT(a.qid) DESC;

As for optimization - LEFT JOIN is usually considered bad unless these tables are pretty small. MySQL basically has to search the whole table for NULL values. It might actually be faster to check if your user has any records in the left joined tables first and only issue a second query using regular JOIN only if you find records.

Hope that helps!