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 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 4-1: Null values in outer join
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.
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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
