When do i use inner joins and when do i use subqueries?

Question

What is the difference between an inner join and a subquery? and when should i use a subquery? or an inner join? I was in a situation recently where an inner join was too slow and subquery was the solution. From my limited understanding i thought the two worked exactly the same.

Answer 1

It doesn't really matter if you use joins or subqueries. You can turn just about any query with a subquery into a query with a join. I'd go with what is more readable. Sometimes, joins are more readable. For other queries, subqueries are more readable. You shouldn't have a big performance difference, as long as you optimize both queries.

One exception I've come across is creating a correlated subquery, where the subqery that is executed depends on the results of the outer query. These types of queries can be useful, but very inefficient. You might use them when you have to calculate data for each row in a query. In this case, you can't use joins.