When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).
Here are two tables that each have NULL in the column that will participate in the join:
table1 table2
a b c d
------- ------ ------- ------
1 one NULL two
NULL three 4 four
4 join4
A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:
SELECT *
FROM table1 t1 JOIN table2 t2
ON t1.a = t2.c
ORDER BY t1.a
Only one row with 4 in column a and c is returned:
a b c d
----------- ------ ----------- ------
4 join4 4 four
(1 row(s) affected)
Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. For example, this SELECT statement does a left outer join on these two tables:
SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
ON t1.a = t2.c
ORDER BY t1.a
Here is the result set:
a b c d
----------- ------ ----------- ------
NULL three NULL NULL
1 one NULL NULL
4 join4 4 four
(3 row(s) affected)
The results do not make it easy 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.
| sp_dbcmptlevel | WHERE |