Explain SQL join types

You are asked to explain the major SQL join types and when you would use each in reporting work. Cover INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN, including what rows each returns, how unmatched rows appear, and how NULL values affect interpretation.

Use practical finance or healthcare reporting examples such as claims, payments, providers, or budget reconciliation. A strong answer should also mention common mistakes, especially how filtering a right-table column in the WHERE clause after a LEFT JOIN can unintentionally remove unmatched rows.

In Providence-style reporting, join choice directly affects metrics such as unpaid claims counts, payment reconciliation, provider attribution, and department-level financial rollups. The interviewer is looking for both SQL correctness and business interpretation.