This discussion is archived
10 Replies Latest reply: Dec 23, 2013 3:44 PM by 976563 RSS

SQL Query Find Row Differences

976563 Newbie
Currently Being Moderated

Hello All!

 

This will most likely be a super-easy question and solution for some one.  Here is my scenario.

 

  • 2 tables (Table_1, Table_2)
  • 3 columns (A, B, C)
  • Table_1 has fewer records than Table_2, so I want to find the records in Table_2 that are not in Table_1
    • I don't care to find records that are in Table_1 and not in Table_2
  • I will make the comparison only with Column A

 

For simplicity sake, lets say Table_1 has 10 total rows and Table_2 has 15 total rows.  It should only find the 5 rows that are in Table_2 and not in Table_1.  Therefore, if I add the number of records found in Table_2 to the total records in Table_1, then I should have at least the same amount of records in Table_1 and Table_2, if not more.  Also, is there a way where I can insert the row differences found into a 3rd "staging" table?

 

Example provided below.

 

Table_1

A | B | C

1 | blahblah | something

2 | blahblah | something

3 | blahblah | something

4 | blahblah | something

5 | blahblah | something

 

Table_2

A | B | C

1 | blahblah | something

3 | blahblah | something

5 | blahblah | something

6 | blahblah | something  <=== Difference

7 | blahblah | something  <=== Difference

 

In reality, there are millions of records in both tables.  I have a workaround for this, but it would require me to do this...Export the results from both Table_1 and Table_2.  Open both results in Excel and run a macro to find the difference.  Once I find the difference, I save it and import the results into the 3rd "staging" table.

 

Any help would be great!  Thanks!

  • 2. Re: SQL Query Find Row Differences
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    You can use a NOT EXISTS or NOT IN sub-query to do that.

    For example, scott.dept and scott.emp are realated by deptno.  To find all the rows in dept that do not have a matching row in emp:

     

    SELECT  *     -- Or any columns you want

    FROM    scott.dept

    WHERE   deptno  NOT IN (

                               SELECT  deptno

                               FROM    scott.emp

                               WHERE   deptno  IS NOT NULL    -- if needed

                           )

    ;

    When using a NOT IN sub-query, make sure that the sub-query can't produce any NULLs.

     

     

    I hope this answers your question.

    If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.  Include an example where column A is the same in both tables, but columns B and C are not.

    Post your best attempt (using any of the solutions posted), and point out where it is getting the wrong results  Explain, using specific examples, how you get the right results from the given data in thsoe places.

    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 3. Re: SQL Query Find Row Differences
    Solomon Yakobson Guru
    Currently Being Moderated

    Assuming TABLE_@ rowes are unique:

     

    SELECT * FROM TABLE_2

    MINUS

    SELECT * FROM TABLE_1

     

    SY.

  • 4. Re: SQL Query Find Row Differences
    Partha Sarathy S Guru
    Currently Being Moderated

    You could use MINUS operator.

     

    WITH T1 AS

    (SELECT 1 C1, 2 C2, 3 C3 FROM DUAL UNION ALL

    SELECT 2,3,4 FROM DUAL UNION ALL

    SELECT 3,4,5 FROM DUAL)

    , T2 AS

    (SELECT 1 C1, 2 C2, 3 C3 FROM DUAL UNION ALL

    SELECT 4,5,6 FROM DUAL UNION ALL

    SELECT 2,3,4 FROM DUAL UNION ALL

    SELECT 6,7,8 FROM DUAL)

    SELECT C1,C2,C3 FROM T2

    MINUS

    SELECT C1,C2,C3 FROM T1;

     

    OUTPUT:

     

     

     

            C1         C2         C3

    ---------- ---------- ----------

             4          5          6

             6          7          8

  • 5. Re: SQL Query Find Row Differences
    SKP Journeyer
    Currently Being Moderated

    with table_1 as(

    select 1 A,'ABC' B , 'XYZ' C from Dual

    UNION ALL

    select 2 A,'ABD' B , 'WYZ' C from Dual

    UNION ALL

    select 3 A,'ABE' B , 'VYZ' C from Dual

    ),

    table_2 as

    (

    select 1 A,'ABC' B , 'XYZ' C from Dual

    UNION ALL

    select 2 A,'ABD' B , 'WYZ' C from Dual

    UNION ALL

    select 3 A,'ABE' B , 'VYZ' C from Dual

    UNION ALL

    select 4 A,'ABF' B , 'UYZ' C from Dual

    UNION ALL

    select 5 A,'ABG' B , 'TYZ' C from Dual

    )

    select A,B,C from table_2

    where  not exists( select 1 from table_1

                      where table_1.A=table_2.A);

     

    A      B      C

    ------  ------ ------

    5    ABG    TYZ

    4    ABF    UYZ

                    

    CREATE TABLE STAGE_1 AS

    SELECT A,B,C from table_2

    where  not exists( select 1 from table_1

                      where table_1.A=table_2.A);

  • 6. Re: SQL Query Find Row Differences
    Solomon Yakobson Guru
    Currently Being Moderated

    We can't use NOT EXISTS, since OP never told us all columns are not null:

     

    SQL> with table_1 as(
      2  select NULL A,'ABC' B , 'XYZ' C from Dual
      3  ),
      4  table_2 as
      5  (
      6  select NULL A,'ABC' B , 'XYZ' C from Dual
      7  )
      8  select A,B,C from table_2
      9  where  not exists( select 1 from table_1
    10                    where table_1.A=table_2.A)
    11  /

    A B   C
    - --- ---
      ABC XYZ

    SQL>

     

    As you can see, NOT EXISTS returns row while tables are identical. That's why we should use MINUS:

     

    SQL> with table_1 as(
      2  select NULL A,'ABC' B , 'XYZ' C from Dual
      3  ),
      4  table_2 as
      5  (
      6  select NULL A,'ABC' B , 'XYZ' C from Dual
      7  )
      8  select A,B,C from table_2
      9  minus
    10  select A,B,C from table_1
    11  /

    no rows selected

    SQL>

     

    SY.

  • 7. Re: SQL Query Find Row Differences
    SKP Journeyer
    Currently Being Moderated

    Thanks Solomon to pointing out the null issue.

     

    So i am modifying the query to:

     

    with table_1 as(

        select NULL A,'ABC' B , 'XYZ' C from Dual

        ),

        table_2 as

        (

        select NULL A,'ABC' B , 'XYZ' C from Dual

        )

        select A,B,C from table_2

        where  not exists( select 1 from table_1

                        where table_1.A=table_2.A)

       and table_2.A is not null

     

    I was also thinking of minus operator to get  the difference

    I will make the comparison only with Column A

     

    But the op has asked for comparison with column A  only

  • 8. Re: SQL Query Find Row Differences
    Solomon Yakobson Guru
    Currently Being Moderated

    SKP wrote:

     

    But the op has asked for comparison with column A  only

    Right, I missed that. Then you are right, we can use NULL adjusted NOT EXISTS:

     

    with table_1 as(

                    select 1 A,'ABC' B , 'XYZ' C from Dual UNION ALL

                    select 2 A,'ABD' B , 'WYZ' C from Dual UNION ALL

                    select 3 A,'ABE' B , 'VYZ' C from Dual

                   ),

        table_2 as (

                    select 1 A,'ABC' B , 'XYZ' C from Dual UNION ALL

                    select 2 A,'ABD' B , 'WYZ' C from Dual UNION ALL

                    select 3 A,'ABE' B , 'VYZ' C from Dual UNION ALL

                    select 4 A,'ABF' B , 'UYZ' C from Dual UNION ALL

                    select 5 A,'ABG' B , 'TYZ' C from Dual UNION ALL

                    select null A,'ABG' B , 'TYZ' C from Dual

                   )

    select  *

      from  table_2

      where not exists(

                       select  1

                         from  table_1

                         where table_1.a = table_2.a

                            or (table_1.a is null and table_2.a is null)

                      )

    /

     

    SY.

     

    Message was edited by: Solomon Yakobson

  • 9. Re: SQL Query Find Row Differences
    976563 Newbie
    Currently Being Moderated

    Thanks everyone for your quick responses!

     

    Sorry for leaving some of the information out.  Oracle Version is 11.2.0.2.  In this case, all of the columns are NOT NULL, but I also only cared to know about column A, so even if B and C were NULL it would have made no difference to me.

     

    Both Frank and Solomon's queries worked for me.  Parth and SKP were helpful too.  I didn't get into the article because the answer was provided in the responses.

     

    Frank's query returned in 11.534 seconds.

    Solomon's query returned in 17.323 seconds.

    Total records 80,813 <== which is the correct number from what I expected the number to be.

     

    Thanks again!  Rewarding as many points as I am allowed.

  • 10. Re: SQL Query Find Row Differences
    976563 Newbie
    Currently Being Moderated

    Next question...how can I insert the 80,813 rows from the result into a 3rd (different) table without having to export it to an Excel and re-importing it through SQL Developer?

     

     

    Nevermind...I got it!

     

    "insert into temp_table ..... ";

     

    Thanks again!

     

    Message was edited by: 976563

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points