Short answer
INNER JOIN returns only rows with a match on both sides; LEFT OUTER keeps all left rows and NULL-fills the right when there's no match (and RIGHT is the mirror); FULL OUTER keeps unmatched rows from both.
Explanation
You need an outer join whenever the absence of a match is meaningful, e.g. 'list all customers and their orders including customers with zero orders'. A classic bug is putting a condition on the outer (right) table in the WHERE clause, which silently turns a LEFT JOIN back into an INNER JOIN; such predicates must go in the ON clause to preserve the unmatched rows.
Common mistakes
- Putting a filter on the right table in WHERE, which turns a LEFT JOIN into an INNER JOIN.
- Confusing LEFT and RIGHT direction.