SQL query with NULL Value Comparison in MS Access

ms-access, sql

Solution

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.

Problem

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?

Original source