What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN in MySQL?

A join decides which rows survive when two tables are matched on a condition.

• INNER JOIN returns only rows that match on both sides. Non-matching rows from either table disappear.
• LEFT JOIN returns every row from the left table, with NULLs filled in where the right table has no match.
• RIGHT JOIN is the mirror image — every row from the right table. It is rarely used in practice because you can always rewrite it as a LEFT JOIN with the tables swapped, which reads more naturally.
• FULL OUTER JOIN returns every row from both sides. MySQL does not support it, so you emulate it with a LEFT JOIN UNIONed to a RIGHT JOIN.

Note: The classic trap is putting a filter on the right-hand table in the WHERE clause of a LEFT JOIN. WHERE b.status = 'active' throws away the NULL rows and silently turns your LEFT JOIN back into an INNER JOIN. Put that condition in the ON clause instead.