How Null Values Affect Joins

Null values in tables or views being joined 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 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 basis to match one unknown value to 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. Each table 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-1 shows two tables, Table t1 and Table t2. Table t1 has two columns, “a” and “b.” Table t2 has two columns, “c” and “d.” Table t1 has three rows: the first shows “1” in the “a” column and “one” in the “b” column. The second row has NULL in the “a” column and “three” in the “b” column. The third row shows “4” in the “a” column and “join4” in the “b”column.. Table t2 has 2rows: the first contains NULL in the “c” column and “two” in the “d” column, and the second shows “4” in the “c” column and “four” in the “d” column.

Table t1 has two columns, “a” and “b.” Table t2 has two columns, “c” and “d,” and so on. 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.