Null values in tables or views being joined will never match each other. Since bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column that is in the inner table.
The result of a join of NULL with any other value is NULL. Because null values represent unknown or inapplicable values, Transact-SQL has no reason to believe that one unknown value matches another.
You can detect the presence of null values in a column from one of the tables being joined only by using an outer join. Here are two tables, each of which has a NULL in the column that will participate in the join. A left outer join displays the null value in the first table.
Figure 6-2: Null values in outer join
Here is the left outer join:
select * from t1, t2 where a *= c
a b c d
----------- ------ ----------- ------
1 one NULL NULL
NULL three NULL NULL
4 join4 4 four
(3 rows affected)
The results make it difficult to distinguish a null in the data from a null that represents a failure to join. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
