SELECT users.name AS username,
count(orders.id) AS orders_count
FROM users
JOIN orders ON orders.user_id=users.id
GROUP BY users.name
Если нужно вывести и пользователей, у которых нет заказов, с нулевым количеством, то использовать
LEFT JOIN.
PS. Запрос из текста вопроса тоже корректен и должен дать правильный результат. Причём с 50% вероятностью он будет преобразован в мой (точнее, оба дадут один и тот же план выполнения). Остальные 50% - на то, что он будет выполнен итерационно (и тогда скорее всего просто будет выполняться дольше).