It really depends, I had situations where I improved some queries by using subqueries.
The factors that I am aware:
- if the subquery uses fields from outer query for comparison or not (correlated or not)
- if the relation between the outer query and sub query is covered by indexes
- if there are no usable indexes on the joins and the subquery is not correlated and returns a small result it might be faster to use it
- i have also run into situations where transforming a query that uses order by into a query that does not use it and than turning it into a simple subquery and sort that improves performance in mysql
Anyway, it is always good to test different variants (with SQL_NO_CACHE please), and turning correlated queries into joins is a good practice.
I would even go so far to call it a very useful practice.
It might be possible that if correlated queries are the first that come to your mind that you are not primarily thinking in terms of set operations, but primarily in terms of procedural operations and when dealing with relational databases it is very useful to fully adopt the set perspective on the data model and transformations on it.
