How does JOIN work in MySQL?

Question

Although the question title is duplicate of many discussions, I did not find a answer to this question:

Consider a simple join for normalized tables of tags as

SELECT tags.tag
FROM tags
    INNER JOIN tag_map
    ON tags.tag_id=tag_map.tag_id
WHERE article_id=xx

Does JOIN work with the entire tables of tags and tag_map then filter the created (JOINed) table to find rows with WHERE clause for the article id

OR JOIN will only join rows of tag_map table in which article_id=xx ?

The latter method should be quite faster!

Answer 1

It will do the former, to my knowledge WHERE's are explicitly performed on the resulting JOINed table. (Disclaimer: MySQL may optimize this in some cases, I don't know).

To force the latter behaviour and execute the WHERE first, you can add an extra filter to your JOIN ON statement:

SELECT tags.tag 
    FROM tags 
    INNER JOIN tag_map 
        ON tags.article_id=xx
        AND tags.tag_id=tag_map.tag_id 
    WHERE article_id=xx