I’d like to understand why the following 2 queries that select duplicates from the companies table have different execution times. The second query with the JOIN is executed much faster.

The query is executed on:

• a companies table with ~200k records
• of which there are 195k unique name column values (each query returns ~1500 results, namely the duplicates)
• there is a nameindex index on the name column

WHERE (0.31142875 s):

SELECT *
FROM `companies`
WHERE `name` IN (
    SELECT `name`
    FROM `companies`
    GROUP BY `name`
    HAVING COUNT(`name`) > 1
);

JOIN (0.07034850 s):

SELECT *
FROM `companies`
INNER JOIN (
    SELECT `name`
    FROM `companies`
    GROUP BY `name`
    HAVING COUNT(`name`) > 1
) AS `duplicate_names` ON `companies`.`name` = `duplicate_names`.`name`;  

Note that the subqueries are exactly the same. Why is it that in this specific setup the second query is faster?

The output from EXPLAIN is:

WHERE query:

JOIN query: