comparing tables and dealing with null value

Posted on 2010-01-07
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
    LVL 14

    Expert Comment

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

    Expert Comment

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

    Accepted Solution


    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


    Author Comment

    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.

    LVL 119

    Expert Comment

    by:Rey Obrero

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

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

    Author Comment

    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 119

    Expert Comment

    by:Rey Obrero
    you will need only two queries
    LVL 119

    Expert Comment

    by:Rey Obrero
    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

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    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.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now