MySQL JOINs with NULL values

Question

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?

VoteyDisciple · Accepted Answer · 2010-11-17 22:36:12Z

3

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

answered Nov 17, 2010 at 22:36

VoteyDisciple

37.9k5 gold badges100 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cfreak · Accepted Answer · 2010-11-17 22:35:32Z

0

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!

answered Nov 17, 2010 at 22:35

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

2 Comments

VoteyDisciple Over a year ago

You're no longer joining to users_experience on the relevant tags; you're counting all experience in all areas. And there's no need to join to users if the uid value is already available (doing so just adds unnecessary processing).

Cfreak Over a year ago

@VoteyDisciple - he wants the user's information where they have experience or not. His original problem stated that when he adds a user ID he didn't get the NULL values. That's because the user ID is null in his user_experience table. I joined tags on users_experience in reverse. It still works. (Note I don't think it's a good idea, I think he should rethink his table design)

Collectives™ on Stack Overflow

MySQL JOINs with NULL values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related