Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Join type determines output cardinality and null semantics. **INNER JOIN**: Only matching rows from both tables; count ≤ min(left, right) when 1:1 or N:1. **LEFT JOIN**: All left rows; right columns NULL where no match; count = left count (if left is...
Architectural Logic: Join type determines output cardinality and null semantics. INNER JOIN: Only matching rows from both tables; count ≤ min(left, right) when 1:1 or N:1. LEFT JOIN: All left rows; right columns NULL where no match; count = left count (if left is 'one' side). Cardinality Rules: INNER can reduce rows (excludes orphans); LEFT preserves left. Use COUNT() for total rows; COUNT(right.key) for non-null matches (LEFT JOIN gives matched count). Why It Matters: Data quality checks—LEFT JOIN with COUNT(right.key) < COUNT() reveals orphaned left rows. Scalability: Join strategy (broadcast, sort-merge) affects shuffle; understanding cardinality informs optimizer hints.
Red Flag: Using INNER when business requires 'all orders including those without customer'—loses data. Pro-Move: Document expected cardinality; add assertions (e.g., dbt test) that row counts match expectations post-join.
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.