mySQL INNER JOIN and Conditions

Question

I wrote a mySQL query that is just works as I need.

SELECT COUNT(wp_posts.ID) as count, wp_terms.term_id
FROM wp_terms 
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id AND wp_term_taxonomy.taxonomy = "post_tag"
INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id 
INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id AND wp_posts.post_date >= DATE_SUB(NOW(),INTERVAL 24 HOUR) AND wp_posts.post_status = "publish" 
GROUP BY wp_terms.term_id ORDER BY count DESC;

As you can see that, I have conditions when I'm joining to tables to eachother.

But I really, what is changes if I also add a WHERE statement to the end?

Like this:

SELECT COUNT(wp_posts.ID) as count, wp_terms.name
FROM wp_terms 
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id AND wp_term_taxonomy.taxonomy = "post_tag"
INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id 
INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id AND wp_posts.post_date >= DATE_SUB(NOW(),INTERVAL 24 HOUR) AND wp_posts.post_status = "publish"
WHERE wp_posts.post_date >= DATE_SUB(NOW(),INTERVAL 24 HOUR) AND wp_posts.post_status = "publish" AND wp_term_taxonomy.taxonomy = "post_tag" 
GROUP BY wp_terms.term_id ORDER BY count DESC;

They both gives me the same results.

I really wonder, what is the diffirince?

Answer 1

Your two queries do not give the same results -- one fetches term_id, one fetches name. (I'll assume that was just a typo...)

The second query unnecessarily repeats the tests on post_date and post_status. Remove from the ON because...

ON tells how two tables are "related". WHERE filters the results. For INNER JOIN there is no functional difference between them. But it helps reading the query to put the tests in one place or the other.

SELECT syntax has a strict order:

SELECT
expression(s) to fetch
FROM
JOIN(s) with ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT (and OFFSET)

If you got the "same" results, then maybe all the taxonomy values are "post_tag"?

You cannot have two WHERE clauses, if that is what you are asking.

Add this plugin: WP Index Improvements

Or, these indexes may help with speed:

wp_terms:  INDEX(term_id,  name)
wp_posts:  INDEX(post_status, post_date, ID)
wp_term_taxonomy:  INDEX(taxonomy, term_id)
wp_term_relationships:  INDEX(term_taxonomy_id,  object_id)