Are JOINs on non-indexed columns faster than sub-queries?

Question

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.

Answer 1

In a query such as this:

SELECT * 
FROM 
  some_table 
  JOIN (SELECT foo, col1 FROM bar) s_sub 
  ON (some_table.col1 = s_sub.col1)

MySQL is able to use an index on some_table.col1. So that it starts with the derived table (full scan), then moves on to some_table using index.

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.