left-join-posts.sql

SELECT *
FROM users
LEFT JOIN (
	SELECT user_id, COUNT(*) AS post_count
	FROM posts
	GROUP BY user_id
) post_counts ON post_counts.user_id = users.user_id

+---------+-------+---------+------------+
| user_id | name  | user_id | post_count |
+---------+-------+---------+------------+
|       1 | Matt  | 1       | 3          |
|       2 | Simon | 2       | 2          |
|       3 | Jen   | NULL    | NULL       |
+---------+-------+---------+------------+