Pente Fino - Dúvidas geral

Olá, Darcio!
Tudo bem?

Sempre que tiver dúvidas, pode perguntar.

Um exemplo:
$categorias = Category::get();

Vamos imaginar que tem 14 categorias cadastradas no banco, logo $categories tem uma collection (array) com 14 registros certo.

No momento de listar as categorias na view quero exibir os produtos dela, como fazer isso?
Assim:
foreach($categorias as $categoria) {
    {{ $categoria->nome }}
    // recuperando os produtos da categoria:
    foreach($categoria->produtos as $produto) {
        {{ $produto->nome }}
    }
}

Isso funciona normalmente.

Problema? Executou uma nova consulta no banco dentro do loop, no caso como temos 14 categorias gerou 14 novas consultas ao banco (um para cada loop).

Solução?
$categorias = Category::with('produtos')->get();

Isso daqui vai gerar apenas 2 querys, um para recuperar as categorias e outra para recuperar os produtos, as querys finais:
select * from categories
select * from produtos where category_id in (1,2,3,4,5...14)

Tudo isso eu entendi ...

O with é como um inner join "ponto final", uma só query que já vai trazer todos os dados evitando subquerys dentro de um Loop bem claro até ...

Avançando nas outras perguntas que fiz do pente fino então:

Tem algo como left join e right join ???

Qual seria o comando quando quero usar um group by, having count ???

Não exatamente um inner join, na realidade gera uma segunda consulta (apenas outra) para buscar os dados relacionados.

Isso:
$categorias = Category::with('produtos')->get();

Gera essas duas consultas (nada de inner join):
select * from categories
select * from produtos where category_id in (1,2,3,4,5...14)

--------------

Você pode usar querys mais complexas usando o Laravel, via código, pode usar o groupBy, innerJoin, distinct e etc: https://laravel.com/docs/7.x/queries#introduction