I was testing performance a little bit for such case:
There is one table called categories with columns id and name, with 10000 records. There is table post with columns id, body, category_id with million rows, where each row was randomly assigned to one of those categories. categories_id in posts is a foreign key pointing to categories.id, and columns named id in both tables are primary keys. indexes are created on those columns.
Now, my goal is to get a list of categories with count of posts in each.
Why is a subquery faster than join? I used h2 and mysql although without too much performance tuning, possibly also without understanding of some matters, and both are consistent in that join is more or less slower than subquery.
Those are the queries:
using subquery:
SELECT categories.name,
(SELECT COUNT(posts.id) FROM posts WHERE posts.categories_id = categories.id) as postCount
FROM categories
join:
select categories.name, count(posts.id) from categories
left join posts on posts.categories_id=categories.id
group by categories.id