comparing tables and dealing with null value


I have two tables, both contain similar data.  table A contains today's values, and table B contains yesterday's values.

I'm trying to create a query that will compare all values in A and B, and find only the items that had changes.

only issue that i am running into is that some fields may contain null, which is keeping securities that have null on both sides to also show up.  e.g.
if field1 is null on both sides
tableA.field1 = tableB.field1 results in false.

how can i go about doing this?  right now, i was planning on creating two queries, one that shows all securities that match, and then from that derive a query that shows only the missing.  
Who is Participating?
Rey Obrero (Capricorn1)Commented:

try this queries
* this will return all securities in tableA that are not in TableB

select tableA.securities, tableB.securities
from tableA left join tableB On tableA.securities=TableB.securities
where tableB.securities is null

* this will return all securities in tableB that are not in TableA

select tableB.securities, tableA.securities
from tableB left join tableA On tableB.securities=TableA.securities
where tableA.securities is null

you can also use the Union to combine the two queries above

How about adding to the WHERE clause:
WHERE (tableA.field1 <> NULL AND tableB.field1 <> NULL)
Or substitute OR for the AND in my previous post if you don't want to deal with any fields that are null.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

iamnamjaAuthor Commented:
i'm not looking to find "missing" securities, and i'm not looking for cases where both fields are showing null.

e.g.      TableA.value      TableB.value
1      A      1      A
2      B      2      
3            3      
4      D      4      Z
5      E      5      E

In the case above, i want a query that will return all values of Table A that have different values than table B.
so in this case, returned result should be:      TableA.value
2      B
4      D

The reason why id: 3 is not included is because they're in my view same.  But since they're both "NULL" its not equating to be the same in sql.  

I hope this clears up some things.  Thanks guys.

Rey Obrero (Capricorn1)Commented:

try the queries i posted to your actual table...

better if you have more question, upload a db with the tables.
iamnamjaAuthor Commented:
Hi Capricorn,

I do see what you mean with your queries, and i'm sorry if i didn't fully explain my situation.

The table contains multiple fields that I need to compare. 9 to be exact.  So any item that has any of the 9 fields not match up should show.  Going by what you have gave an example of, I would need to create 15 queries to get the result that I am expecting.  I'm hoping that there will be a simpler solution.

As for uploading the database, I can't do that as this is a sensitive material that I am working on, and besides all this is based off of a linked table.

The example above I was hoping would give an idea of what the issue is.  If you require additional info, please let me know.
Rey Obrero (Capricorn1)Commented:
you will need only two queries
Rey Obrero (Capricorn1)Commented:
try this first

SELECT TableA.f1, TableA.f2, TableA.f3, TableA.f4, TableA.f5, TableA.f6, TableA.f7, TableA.f8, TableA.f9, TableB.f1, TableB.f2, TableB.f3, TableB.f4, TableB.f5, TableB.f6, TableB.f7, TableB.f8, TableB.f9
FROM TableA LEFT JOIN TableB ON (TableA.f9=TableB.f9) AND (TableA.f8=TableB.f8) AND (TableA.f7=TableB.f7) AND (TableA.f6=TableB.f6) AND (TableA.f5=TableB.f5) AND (TableA.f4=TableB.f4) AND (TableA.f3=TableB.f3) AND (TableA.f2=TableB.f2) AND (TableA.f1=TableB.f1)
WHERE (((TableB.f1) Is Null) AND ((TableB.f2) Is Null) AND ((TableB.f3) Is Null) AND ((TableB.f4) Is Null) AND ((TableB.f5) Is Null) AND ((TableB.f6) Is Null) AND ((TableB.f7) Is Null) AND ((TableB.f8) Is Null) AND ((TableB.f9) Is Null));
Nz(tableA.field1) = Nz(tableB.field1)
will match if broth sides are null.

Where tableA.field1 Is Null or tableB.field1 Is Null
will find the blank fields
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.