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.

Answer 2

In SQL, not only is NULL not equal to anything, it’s also not unequal to anything.

Any comparison with NULL (except IS NULL) results in an UNKNOWN value, so no row will be returned.

As an example (in this case using MySQL syntax, but the same goes for Access);

SELECT 1 FROM DUAL WHERE 0 = 0;
SELECT 2 FROM DUAL WHERE 0 <> 1;
SELECT 3 FROM DUAL WHERE NULL = NULL;
SELECT 4 FROM DUAL WHERE NULL <> 1;

Query 1 and 2 will obviously return 1 and 2 respectively, but query 3 and 4 will (less obviously) return nothing.

More about this in Access at this link (scroll to "Error 5").

Answer 3

For MS SQL:

WHERE isnull(Table1.Column4,'') <> isnull(Table2.Column4,'')

Or, to be more standard for other SQL implementations, use 'coalesce' instead of 'isnull'

Answer 4

Null is weird. Null is not equal to anything (even null), and it is not unequal to anything (even null). The easiest solution is to turn the null into a not-null. In Access, use the nz() function and a known not-real value.

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 nz(Table1.Column4,'~~~') <> nz(Table2.Column4,'~~~')

(In Oracle, use nvl().)

The problem isn't with the join. In Table1 and Table2, (Column1, Column2, Column3) is the primary key, and cannot have nulls. An inner join is absolutely appropriate -- a left join would also give you all Table1 records that don't have a matching Table2 record, which is not what you want.