comparing tables and dealing with null value

Posted on 2010-01-07
Medium Priority
Last Modified: 2013-11-28

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.  
Question by:iamnamja
  • 4
  • 2
  • 2
  • +1
LVL 14

Expert Comment

ID: 26205010
How about adding to the WHERE clause:
WHERE (tableA.field1 <> NULL AND tableB.field1 <> NULL)
LVL 14

Expert Comment

ID: 26205029
Or substitute OR for the AND in my previous post if you don't want to deal with any fields that are null.
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 26205069

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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Author Comment

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

TableA.id      TableA.value      TableB.id      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.id      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.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26205146

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

better if you have more question, upload a db with the tables.

Author Comment

ID: 26205246
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.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26205355
you will need only two queries
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26205410
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));
LVL 39

Expert Comment

ID: 26205569
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question