INNER JOIN and LEFT JOIN

Suppose authors contains Ada and Lin, but only Ada has a post.

An inner join returns matching pairs:

SELECT authors.name, posts.title
FROM authors
INNER JOIN posts ON posts.author_id = authors.id;

The result contains Ada and her post. Lin disappears because no post row matches.

A left join keeps every row from the left table:

SELECT authors.name, posts.title
FROM authors
LEFT JOIN posts ON posts.author_id = authors.id;

Lin now appears with a NULL title. That NULL was produced by the missing match; it is not a stored post.

Use an inner join when the result requires related rows. Use a left join when the absence is meaningful, such as finding authors with no posts:

SELECT authors.name
FROM authors
LEFT JOIN posts ON posts.author_id = authors.id
WHERE posts.id IS NULL;

Be careful when filtering the right table. This condition removes NULL matches and effectively turns the left join into an inner join:

WHERE posts.status = 'published'

When the requirement is “all authors, plus their published posts,” place that condition in the join:

LEFT JOIN posts
  ON posts.author_id = authors.id
  AND posts.status = 'published'

One-to-many joins can return several rows for one author. That is correct when the author has several posts; do not add DISTINCT automatically.

Your action is to run both joins with one author who has no posts. Then move a right-table filter between WHERE and ON and explain the changed result.