SQL query with NULL Value Comparison in MS Access

Question

I am trying to compare two tables and output results where a column in table1 and the same column in table2 are not equal.

The query looks like this:

Select Table1.Column1, Table1.Column2, Table1.Column3, Table1.Column4
from Table1 INNER JOIN
     Table2
     ON Table1.Column1 = Table2.Column1 AND
        Table1.Column2 = Table2.Column2 AND
        Table1.Column3 = Table2.Column3
WHERE Table1.Column4 <> Table2.Column4;

Column1, Column2, Column3 together form the primary key for the two tables.

When Column4 has missing values (null), the corresponding record is not showing up as a mismatch in the resulting output.

(This is happening with other columns as well, be it Text or Number or Date/Time data type)

Any Comments?

Answer 1

You need a LEFT Join, not an INNER join.

from Table1 LEFT JOIN

You might like to say:

Select Table1.Column1, Table1.Column2, Table1.Column3, Table1.Column4
from Table1 LEFT JOIN
     Table2
     ON Table1.Column1 = Table2.Column1 AND
        Table1.Column2 = Table2.Column2 AND
        Table1.Column3 = Table2.Column3
WHERE Table1.Column4 & "" <> Table2.Column4 & "";

Concatenating a zero-length string with the field will ensure that a comparison between a value and null will show up.