LEFT JOIN

A LEFT JOIN keeps every row from the left table, filling the right side with NULLs where there is no match.

Use it whenever the absence is part of what you want to know: authors with no books, orders with no payment, students with no submission. An inner join answers a different question and answers it silently.

Bessie Head appears with a NULL title. Change LEFT JOIN to JOIN and she vanishes: same data, different question.

✦ COUNT(*) versus COUNT(b.id) after a LEFT JOIN

An author with no books produces one row full of NULLs. COUNT(*) counts that row and reports 1 book. COUNT(b.id) skips the NULL and correctly reports 0. This is the most common wrong number in any 'how many each' query.