How to get rows that r not common in two tables?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to get rows that r not common in two tables?

  1. #1
    Join Date
    Mar 2008
    Posts
    12

    How to get rows that r not common in two tables?

    hi ,
    I have two tables report and report_old having same table structure.Data in both is different.Some rows are common in both while others are different.
    MSISDN NUMBER,
    EXPIRY_DATE DATE,
    VAL_BKT VARCHAR2(4000),
    ACCT_BALANCE NUMBER,
    BAL_BKT VARCHAR2(4000)
    I want to get to get all the msisdn in report table that are not in the report_old table.I use below query for that but its taking long time execute.
    create table report_ins
    as
    (select *From report
    where msisdn not in (select msisdn from report_old));
    Both the table contain more than 360000 rows of data .
    Plz help.

  2. #2
    Join Date
    Apr 2008
    Posts
    6

    Wink try with exists operator

    try with this query .. it will reduce the access time but not sure how much.. but it is good query compared to older one.
    create table report_ins
    as
    (select *From report a
    where not exists (select b.msisdn from report_old b where a.msisdn=b.msisdn));

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    you can do it easily with MINUS operator, follows pseudocode

    Code:
    select * from table_a
    minus
    select * from table_b
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2008
    Posts
    12
    Thanks 'not exists' has improved performance..

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