Adriano, boa tarde!
Uma subquery é utilizada quando você precisa retornar uma informação, mas não pode utilizar joins para isso.
No exemplo que você passou, você precisou retornar uma média e fazer outras operações, em todos os registros de uma tabela (tendo em vista que não foi utilizada a clausula where na subquery).
Se você fosse fazer a mesma coisa com o join, não daria certo por causa do agrupamento que os dados teriam.
E em muitos casos, não é viável fazer várias consultas ao banco, às vezes precisamos de tudo seja retornado em um único select, e nesses casos usamos subquery.
podemos usar também em outros lugares como em uma where.
algo como:
SELECT
p.nome,
p.email
FROM tb_pessoa p
INNER JOIN tb_compras c
ON c.pessoa_id = p.id
WHERE
p.ativo = 1 AND
c.status = 1 AND
p.tipo IN( SELECT t.tipo FROM tb_tipo_pessoa t WHERE t.status = 1 AND t.grupo = 'g' GROUP BY t.tipo );
Veja que no meu exemplo, eu preciso que as pessoas listadas, possuam um tipo específico, mas para determinar esse tipo, tenho outros wheres que não podem estar na query principal.
