If there are null values in the columns of the tables being joined, the null values will never match each other. 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 NULL in the column that will participate in the join. A left outer join (created using *=) displays the NULL in the first table:
table1 |
table2 |
|||
a |
b |
c |
d |
|
------- |
------ |
------- |
------ |
|
1 |
one |
NULL |
two |
|
NULL |
three |
4 |
four |
|
4 |
join4 |
A left outer join on these two tables looks like this:
SELECT *
FROM table1, table2
WHERE a *= c
a b c d
--------- ------ -------- ------
1 one NULL NULL
NULL three NULL NULL
4 join4 4 four
Note that the results do not make it particularly 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.