The LEFT OUTER JOIN

May 7, 2007

So let’s say you’re fetching posts from your blog app and also want to show the comment count for every post. You can’t use a typical JOIN because some posts may not have any comments at all and doing a separate query in the comments table is just plain wasteful. Use a LEFT OUTER JOIN instead:

SELECT *, COUNT(comments.id) AS comment_count

FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id)

GROUP BY posts.id

ORDER BY date_added DESC

LIMIT 0, 20