INNER JOIN and Matching Rows
Why are your INNER JOINs dropping rows?
Did you know that a predicate in T-SQL can return one of three results: TRUE (1=1), FALSE (1=2), and UNKNOWN (1=NULL or NULL=NULL)? The UNKNOWN result occurs when a predicate involves NULLs, which prevents a definitive TRUE or FALSE outcome.
Understanding how NULLs affect your query results, particularly for JOIN predicates in the ON clause, is crucial for getting expected results.
When you perform an INNER JOIN, if NULLs exist in the columns used in the JOIN condition, they are excluded from the results because comparing one NULL to another value results in an UNKNOWN.
Imagine you have two baskets of red apples. Each apple may have a label, such as "McIntosh" or "Fuji," while others may have no label at all (NULL). You want to match the apples from both baskets by their labels; this is your INNER JOIN.
When an apple has no label, you can't match it with another apple. So, the apples without labels get left out.
To include all the apples from the first basket, even the unlabeled ones, use a LEFT JOIN.