Data Comparison SQL

Posted on 2012-08-14
Last Modified: 2012-08-15
Hi Experts,

Today I have the task of comparing data from two tables, this has been the process so far.

Write SQL Select for data source
Write SQL Select for data target

So I have two tables, table A and Table B.

They have the same amount of columns but they have different names (at this point).

What technique is recommended for this process of collecting records which match?

Thank you
Question by:BIT_BOPPER
    LVL 11

    Expert Comment

    by:Larissa T
    You need to know on what column(s) you need to compare, and if columns can have nulls.

    here is comparison based on 2 columns that do not allow nulls

    -- data present in both tables
    select a.*, b.*
    from A join B on a.ac1=b.bc1 and a.ac2=b.bc2
    -- data present in table a only
    select a.*, b.*
    from A
    left join B on a.ac1=b.bc1 and a.ac2=b.bc2
    where b.bc1 is null and b.bc2 is null

    -- data present in table B only
    select a.*, b.*
    from B
    left join A on a.ac1=b.bc1 and a.ac2=b.bc2
    where a.ac1 is null and a.ac2 is null
    LVL 25

    Accepted Solution

    You might be able to use INTERSECT to find the rows in common between both tables

    SELECT * FROM table_A
    SELECT * FROM table_B


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    How to increase the row limit in Jasper Server.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now