How to make a subquery on AVG from another mySQL table?

Question

MySQL

There is a table of products (`prods`):
`prod_id`, `prod_name`, `prod_desc`, etc...
And a table of product reviews (`reviews`):
`rev_id`, `rev_prod`, `rev_rating`, `rev_text` etc...
Relationship: `reviews`.`rev_prod` => `prods`.`prod_id`
The `rev_rating` field contains the "rating" of the product (number from 1 to 5)
I need to get it in one request average rating for each product...
I do this:

SELECT `prod_id`, `prod_name`, `prod_desc`, AVG(`rev_rating`) AS `avg_rating` 
FROM prods p
JOIN reviews r ON p.prod_id = r.rev_prod
GROUP BY prod_id;

Everything is beautiful, BUT! - thus, in the issue there will be no products for which there is not a single review ...

Answer 1

LEFT JOIN of the product table with the rating table grouped in the subquery.