INNER JOIN and LEFT JOIN

Suppose authors contains Ada and Lin, but only Ada has a post titled “Learning SQL”.

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;

That returns Lin.

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. The extra rows are real data, not a bug.

The table on the left side of the join is the one a left join preserves. Swap the table order and you change which rows survive with NULL matches.

Inner joins are the default when you write JOIN without a qualifier. INNER JOIN and JOIN mean the same thing in standard SQL. I use INNER JOIN in teaching examples because the intent is explicit.

Try this on your own: 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.