Row comparision for 2 identical tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Row comparision for 2 identical tables

Hybrid View

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Row comparision for 2 identical tables

    Hi Guys,

    Been asked to look at row comparision in 2 identical tables, and report on rows with differenct data.

    For example, I have 2 tables of identical structure - table1 and table2.

    Create table TABLE1 (or TABLE2)
    ( cust_num number(4),
    favourite_colour varchar2(10),
    favourite_car varchar2(10))

    The primary key on both is cust_num.

    I need to output the cust_num and the values on both tables, where the values do not match for the primary key. The code should only output the columns where a difference exists.

    For example -

    CUST_NUM TABLE1.FAVOURITE_COLOUR TABLE2.FAVOURITE_COLOUR
    -------- ----------------------- -----------------------

    123 RED BLUE




    Can anyone give me starter (or complete solution, if you're feeling generous). I'm scheduled to look at this tomorrow, and would appreciate any early input.

    (ps - this is not my homework!!)

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Are you looking to find rows where:

    A). the primary key is different, but all the rest of the data is the same (so a row from table1 is related to a row in table2).

    Code:
    15:01:53 SQL> create table temp_table1 (cust_num number(4),
    15:03:03   2  favourite_colour varchar2(10),
    15:03:03   3  favourite_car varchar2(10),
    15:03:03   4  constraint temp_table1_pk 
    15:03:03   5  primary key (cust_num));
    
    Table created.
    
    15:03:40 SQL> create table temp_table2 (cust_num number(4),
    15:03:50   2  favourite_colour varchar2(10),
    15:03:50   3  favourite_car varchar2(10),
    15:03:50   4  constraint temp_table2_pk 
    15:03:50   5  primary key (cust_num));
    
    Table created.
    
    15:20:06 SQL> select * from temp_table1;
    
      CUST_NUM FAVOURITE_ FAVOURITE_
    ---------- ---------- ----------
             1 red        porsche
             2 blue       ferrari
             4 orange     Skoda
    
    15:20:19 SQL> select * from temp_table2;
    
      CUST_NUM FAVOURITE_ FAVOURITE_
    ---------- ---------- ----------
             1 red        porsche
             3 blue       ferrari
             5 orange     Beetle
    
    15:20:39 SQL> select a.*, b.*
    15:20:57   2  from (select * from temp_table1 where cust_num not in(
    15:20:57   3   select cust_num from temp_table2)) a,
    15:20:57   4       (select * from temp_table2 where cust_num not in(
    15:20:57   5   select cust_num from temp_table1)) b
    15:20:57   6  where a.favourite_colour=b.favourite_colour
    15:20:57   7   and a.favourite_car=b.favourite_car;
    
      CUST_NUM FAVOURITE_ FAVOURITE_   CUST_NUM FAVOURITE_ FAVOURITE_
    ---------- ---------- ---------- ---------- ---------- ----------
             2 blue       ferrari             3 blue       ferrari
    
    15:20:58 SQL>
    or are you looking for:

    B). Any row that that is in one table, but not in the other and the rows bear no relation to each other?...

  3. #3
    Join Date
    Feb 2004
    Location
    Russia
    Posts
    13
    (select cust_num, favourite_colour from table1
    minus
    select cust_num, favourite_colour from table2)
    union all
    (select cust_num, favourite_colour from table2
    minus
    select cust_num, favourite_colour from table1);

    ???

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =======
    (select cust_num, favourite_colour from table1
    minus
    select cust_num, favourite_colour from table2)
    union all
    (select cust_num, favourite_colour from table2
    minus
    select cust_num, favourite_colour from table1);

    ========
    The above SQL is to run slow.
    There will 2 full table scans on table1 and another 2 full table scans on table2. Isn't it bad?

    This above SQL was modified by Tom and another brilliant Italian (I forgot his name). Their version is given below:

    PHP Code:
    select c1c2c3count(src1), count(src2)
    from
    (
        
    select a.*, 1 SRC1to_number(nullSRC2 
        from   a
        union  all
        select b
    .* , to_number(nullSRC12 SRC2
        from   b
    )
    group by c1c2c3
    having count
    (src1) <> count(src2)

    Tamil

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    This above SQL was modified by Tom and another brilliant Italian (I forgot his name). Their version is given below:
    http://asktom.oracle.com/~tkyte/compare.html
    Last edited by jmodic; 01-31-2005 at 06:11 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks for the link to Tom's page. I'm still working through all that.

    What I need is......

    Assuming both tables primary keys are identical and same rowcount in tables, for the same primary key match, I need to output if col1 in table1 is different to col1 in table2, OR if col2 in table1 is different to col2 in table2, OR if col3.......etc.

    Output would look something like -

    For Cust_num 12345, table1.col3 has the value RED, but table2.col3 has the value BLUE

  7. #7
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Too obvious to be what you are looking for Horace, but it seems to match your requirements in the last post...

    Code:
    11:23:40 SQL> select * from temp_table1;
    
      CUST_NUM FAVOURITE_ FAVOURITE_                                 
    ---------- ---------- ----------                                 
             1 red        porsche                                    
             2 blue       ferrari                                    
             4 orange     Skoda                                      
             6 purple     mini                                       
    
    11:23:47 SQL> select * from temp_table2;
    
      CUST_NUM FAVOURITE_ FAVOURITE_                                 
    ---------- ---------- ----------                                 
             1 red        porsche                                    
             3 blue       ferrari                                    
             5 orange     Beetle                                     
             6 red        mini                                       
    
    11:23:52 SQL> select a.*, b.*
    11:23:59   2  from temp_table1 a,
    11:23:59   3       temp_table2 b
    11:23:59   4  where a.cust_num = b.cust_num
    11:23:59   5   and (a.favourite_colour<>b.favourite_colour
    11:23:59   6   or a.favourite_car<>b.favourite_car);
    
      CUST_NUM FAVOURITE_ FAVOURITE_   CUST_NUM FAVOURITE_ FAVOURITE_
    ---------- ---------- ---------- ---------- ---------- ----------
             6 purple     mini                6 red        mini      
    
    11:24:00 SQL> spool off

  8. #8
    Join Date
    Jan 2004
    Posts
    162
    What would you output if both columns were different? Two rows or perhaps an essay?

  9. #9
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    That's the problem padders. I've used a small table as an example. The actual tables have 70 columns each. The developers are sure that the tables should match identically, but we (the dba team) would like to prove, rather than assume.

    We'd also like a readable output. With the assumption that the tables are 'near' identical, the output shouldn't be an essay!!

  10. #10
    Join Date
    Jan 2004
    Posts
    162
    OK, no essay. That bit was a joke.

    Now how about you tell me what would you output if both (or n) columns were different? Two (or n) rows ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width