Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Inner**: Matches only. **Left/Right**: Preserve one side; null for no match. **Full outer**: All from both. **Cross**: Cartesian product. **Left semi**: Rows in left with match in right (EXISTS). **Left anti**: Rows in left with no match (NOT EXISTS). **Why each**: Inner for...
Inner: Matches only. Left/Right: Preserve one side; null for no match. Full outer: All from both. Cross: Cartesian product. Left semi: Rows in left with match in right (EXISTS). Left anti: Rows in left with no match (NOT EXISTS). Why each: Inner for fact-dim; left for optional dimension; semi/anti for filtering without bringing right columns. Scalability: Broadcast for small side (<10MB); sort-merge for large-large; shuffle hash default. Cost: Cross join on large tables = disaster; broadcast avoids shuffle.
Red Flag: Cross join without explicit intent. Pro-Move: 'We used left_anti to filter invalid user_ids before fact insert—avoided 200GB shuffle from NOT IN subquery.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.