Reviewed by Aditya Kumar · Last reviewed 2026-03-24
INNER JOIN with NULL: NULL keys don't match (NULL = NULL is false), so they're excluded. Count: SELECT COUNT(*) FROM a INNER JOIN b ON a.key = b.key. If key has nulls, inner join count is less than min(|a|,|b|). To include null matches: ON (a.key = b.key OR (a.key IS NULL AND...
INNER JOIN with NULL: NULL keys don't match (NULL = NULL is false), so they're excluded. Count: SELECT COUNT(*) FROM a INNER JOIN b ON a.key = b.key. If key has nulls, inner join count is less than min(|a|,|b|). To include null matches: ON (a.key = b.key OR (a.key IS NULL AND b.key IS NULL)). Count depends on null handling. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.
Red Flag: Using null keys in joins without explicit handling—NULL=NULL is UNKNOWN. Pro-Move: 'We normalized with COALESCE(key,-1) in join; documented null semantics in data contract.'
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.