I know JOIN on indexed columns are faster than sub-queries in a query result.
I am confused if : JOINs on non-indexed columns are also faster than sub-queries or not.
In a query such as this:
MySQL is able to use an index on
This means it can utilize an index. So a subquery does not altogether negates use of an index.
If no index exists on any relevant column, obviously hard work is done (full scan, join buffer) to make the JOIN.