Mysql multiple joins to one table with different conditions -- where do they go?

Question

I am querying for some posts in the wordpress posts table and grabbing multiple meta values with it by joining the postmeta table multiple times. I also have a number of conditions for both post columns and meta values. The query seems to be working but I am curious if I have put the conditions in the correct place. Comparing the following queries, which will get me the post ids and their associated meta most effectively/efficiently?

This one:

SELECT a.ID as `post_id`, b.meta_value as `metaval1`, c.meta_value as `metaval2`, d.meta_value as `metaval3`
FROM posts AS a
JOIN postmeta AS b ON (b.post_id = a.ID AND b.meta_key = 'metakey1')
LEFT JOIN postmeta as c ON (c.post_id = a.ID AND c.meta_key = 'metakey2')
LEFT JOIN postmeta as d ON (d.post_id = a.ID AND d.meta_key = 'metakey3')
WHERE
    b.meta_value != 10
    AND a.post_status = 'pending'
    AND a.post_date < '2013-03-07 00:00:00'
    AND a.post_type = "post"
ORDER BY a.post_date DESC
LIMIT 100;

OR this one:

SELECT a.ID as `post_id`, b.meta_value as `metaval1`, c.meta_value as `metaval2`, d.meta_value as `metaval3`
FROM posts AS a
JOIN postmeta AS b ON (b.post_id = a.ID)
LEFT JOIN postmeta as c ON (c.post_id = a.ID)
LEFT JOIN postmeta as d ON (d.post_id = a.ID)
WHERE
    b.meta_key = 'metakey1'
    AND b.meta_value != 10
    AND c.meta_key = 'metakey2'
    AND d.meta_key = 'metakey3'
    AND a.post_status = 'pending'
    AND a.post_date < '2013-03-07 00:00:00'
    AND a.post_type = "post"
ORDER BY a.post_date DESC
LIMIT 100;

Any advice would be appreciated :)

possible duplicate of [SQL JOIN where to place the WHERE condition?](http://stackoverflow.com/questions/15483808/sql-join-where-to-place-the-where-condition) The second one is incorrect, see why in the link I provide. — Sebas, Mar 22 '13 at 19:00
Related, yeah, and mostly a duplicate. In general, it's best to place as many of the relevant conditions as possible into the `JOIN` condition, _especially_ when dealing with some sort of outer join. Believe me, future maintainers will thank you. And potentially your optimizer. — Clockwork-Muse, Mar 22 '13 at 19:58
Haha, man, I swear I looked up and down for this first. Seems I included too much detail in the search query. — technosis, Mar 22 '13 at 20:41
that's ok, that's also the role of the community here to provide related answers. — Sebas, Mar 22 '13 at 22:27

score 0 · Accepted Answer · answered Mar 22 '13 at 20:15

There is a difference bewteen your two queries.

The first query will return values for a post, regardless of the number of metakey values that match (at least for posts where the "b" value is not 10). You are doing the comparison in the on clause, so the left join processes the results.

The second query will only return values when all three keys are present. The comparison is in the where clause, so non-matching rows generated by the left join will be filtered out.

Which you prefer depends on what you want the results to be. Both should take about the same amount of computing effort.

If you want efficiency, you should also try:

SELECT p.id,
       max(case when pm.meta_key = 'metakey1' then pm.meta_value end) as metavalue1,
       max(case when pm.meta_key = 'metakey2' then pm.meta_value end) as metavalue2,
       max(case when pm.meta_key = 'metakey3' then pm.meta_value end) as metavalue3
FROM posts p join
     postmeta pm
     on pm.post_id = p.ID
WHERE pm.meta_key in ('metakey1', 'metakey2', 'metakey3') and
      (pm.meta_key <> 'metakey1' or pm.meta_value != 10) and
      p.post_status = 'pending' and
      p.post_date < '2013-03-07 00:00:00' and
      p.post_type = 'post'
group by p.post_date, p.id
ORDER BY p.post_date DESC
LIMIT 100;

This limits the number of joins, using aggregation to bring the results together. Your original query has to scan through the results to do the order by -- the aggregation should be about the same amount of work, but with fewer joins.

Excellent, thanks for the explanation. So if I wanted only results that had all 3 meta values, I would use query #2? Would making all the joins innner joins have the same effect in query #1? — technosis, Mar 22 '13 at 20:40
@technosis . . . If you want all three, then I would recommend changing the joins to inner joins and using either query. Don't use `left join` and depend on the `where` clause to undo it -- it makes the query confusing. — Gordon Linoff, Mar 22 '13 at 20:41

Mysql multiple joins to one table with different conditions -- where do they go?

1 Answers1