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

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

• INNER JOIN — only rows matching on both sides. Non-matching rows from either table disappear.
• LEFT JOIN — every row from the left table, with NULLs where the right has no match. Use it when you must not lose rows from your primary table.
• RIGHT JOIN — the mirror image. Rarely used, because swapping the tables and using LEFT reads more naturally.
• FULL OUTER JOIN — every row from both sides, with NULLs filling the gaps. Useful for reconciliation, finding records present in one system and not the other.
• CROSS JOIN — every combination of both tables. Occasionally deliberate, for generating a date-by-product grid; usually an accident.

The two traps that matter in real analysis:

• A WHERE condition on the right table turns a LEFT JOIN into an INNER JOIN. WHERE b.status = 'active' discards the NULL rows you were trying to keep. Put the condition in the ON clause instead.
• Joining on a non-unique key multiplies rows. If the right table has three rows per key, your row count triples and every SUM is inflated. Always check the row count before and after a join — this is the most common source of wrong numbers in analysis.