How to join with null value?

This code returns only posts having a rating. How I can get all posts with null value where the post does not have a rating?

select
posts.id,
posts.user_id,
posts.name,
posts.created_at,
ratings.post_id,
ratings.avg,
ratings.count
from
posts
join (
select
post_id,
avg(rating) as avg,
count(rating) as count
from
ratings
group by
ratings.post_id
) ratings
on ratings.post_id = posts.id


Have you tried using a LEFT OUTER JOIN? A LEFT OUTER JOIN will keep all the rows from the 'left' side of the join statement (posts table) that do not have a correponding row on the 'right' side of the statement (ratings table). The missing rows from the 'right' side will be filled with NULLS.

StackExchange Database Administrators Q#151201, answer score: 8