4

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?

Improve this question
0

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

or just 'WHERE table2.column4 is null'
@Beth, that will not capture the case where table1.column4 is null and it would need to be an addition to the where statement to compare when the fields are not null.
I assumed he was referring to table2.column4, but if he has null values in table1.column4 and wants all non-null table2.column4 rows to show, then yes, he'd need to do what you describe.
Hi Remou, I was using LEFT JOIN actually, But I think & "" seems to fix the problem, can you please explain what it does?
It changes a null into a zero length string. "abc" & "" = "abc" but null & "" = "". You can compare "abc" with "", but not with null.
|
1

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").

Improve this answer

Comments

0

For MS SQL:

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

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

Improve this answer

1 Comment

Does this apply to all data types, numeric, text, date/time & Boolean (Yes/No)?
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.