13 Replies Latest reply: Feb 21, 2014 5:19 AM by Stew Ashton RSS

compare two tables

don123 Newbie
Currently Being Moderated

Hello experts, I want to compare two tables as per requirement.

 

(2) The first comparison

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

 

select field1, field2 from table1 where field1='ABCD' minus select field1, field2 from table2 where field1='ABCD';

The above SQL works fine. I want to execute this for all different values of field1 in table1 and table2. How to do this ? Shall i use for loop in PLSQL ?

 

 

(2) The second comparison

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

select a.field1, a.field2 from table1 a, table2 b where a.field3=b.field3 and a.field4=b.field4 and a.field1='ABCD' and b.field1='ABCD';

I want to pass a.field1='ABCD'  and b.field1='ABCD' from first comparison mentioned above and it should iterate for all values of field1 in table1 and field1 in table2.

 

Please suggest me how to do this. The fileds field1, field2, field3, field4 in both table1 and table2 are varchar2 with same structure.

 

 

Thanks

 

 

 


  • 1. Re: compare two tables
    Prashant Dabral Explorer
    Currently Being Moderated

    Hi,

     

    Can you show sample data and your require output in tabular format.

     

    Regards,

    Prashant

  • 2. Re: compare two tables
    don123 Newbie
    Currently Being Moderated

    SQL> select field1, field2, field3, field4 from table1 where field1='ABCD';


    ABCD RW11L S15514887 W047553920
    ABCD RW11R S15525042 W047562394
    ABCD RW29L S15524330 W047543328
    ABCD RW29R S15514195 W047535189


    SQL> select field1, field2, field3, field4 from table2 where field1='ABCD';


    ---- ----- --------- ----------
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW11C S15514887 W047553920
    ABCD RW11  S15525042 W047562394
    ABCD RW29C S15524330 W047543328
    ABCD RW29  S15514195 W047535189
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW29L S15524330 W047543328
    ABCD RW11C S15514887 W047553920
    ABCD RW11  S15525042 W047562394
    ABCD RW29C S15524330 W047543328
    ABCD RW29  S15514195 W047535189
    ABCD RW29L S15524330 W047543328


    regards

  • 3. Re: compare two tables
    Prashant Dabral Explorer
    Currently Being Moderated

    Hi,

     

    Below mentioned queries can help you out in finding the solution, below queries are cross checked with your first solution of getting result set on minus operations.

     

    Field 2

    SELECT *
      FROM TAB1 a
    where not exists (select 1
                        from TAB2 b
                       where a.field1=b.field1
                         and a.field2=b.field2);

    Field 3

    SELECT *
      FROM TAB1 a
    where not exists (select 1
                        from TAB2 b
                       where a.field1=b.field1
                         and a.field3=b.field3);                     

    Field 4

    SELECT *
      FROM TAB1 a
    where not exists (select 1
                        from TAB2 b
                       where a.field1=b.field1
                         and a.field4=b.field4);

     

    Regards,

    Prashant Dabral

  • 4. Re: compare two tables
    don123 Newbie
    Currently Being Moderated

    HI Prashant Thanks

     

    I have created two new tables with field1 values only 'ABCD' and tested your SQL, it works fine.

    If i run the same SQL for entire table with different values in field1 (for example, KABY, PQBJ, KIOP etc), it gives incorrect result.

     

     

     

     


  • 5. Re: compare two tables
    AnnPricks E Guru
    Currently Being Moderated

    I have changed your query from what i understood and i didn't tested. If you provide create table and insert script,required output then i will test this and give to you. So can you please try the below query and let me know in case of any issues.

    First Query:-

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

    select field1, field2 from table1 where field1='ABCD' minus select field1, field2 from table2 where field1='ABCD';

     

    Modified Query:-

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

    SELECT t1.field1,

           t1.field2

    FROM table1 t1,table2 t2

    WHERE t1.field1 = t2.field1

    AND t1.field2 <> t2.field2;

     

    (OR)

    SELECT field1,

                 field2

    FROM table1 t1

    WHERE EXISTS(SELECT 1

                 FROM table2 t2

                 WHERE t1.field1 = t2.field1)

    AND NOT EXISTS(SELECT 1

                   FROM table2 t2

                   WHERE t1.field2 = t2.field2);


     

    Second Query:-

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

    select a.field1, a.field2 from table1 a, table2 b where a.field3=b.field3 and a.field4=b.field4 and a.field1='ABCD' and b.field1='ABCD';


    Modified Query:-

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

    SELECT a.field1,

           a.field2

    FROM table1 a,table2 b

    WHERE a.field1 = b.field1

    AND a.field3 = b.field3

    AND a.field4 = b.field4

    AND a.field2 <> b.field2;


    (OR)

     

    SELECT field1,

           field2

    FROM table1 a,table2 b

    WHERE a.field1 = b.field1

    AND a.field3 = b.field3

    AND a.field4 = b.field4

    AND NOT EXISTS(SELECT 1

                   FROM table2 t2

                   WHERE a.field2 = t2.field2)

    AND NOT EXISTS(SELECT 1

                   FROM table1 t1

                   WHERE b.field2 = t1.field2);

     

  • 6. Re: compare two tables
    don123 Newbie
    Currently Being Moderated

    Hi, Thanks

     

    The data is given third thread.

     

    (1) First I am trying to select field1 and field2 that are present table1 but not present in table2, so the output is given below.

     

    ABCD      RW11L     

    ABCD      RW11R    

    ABCD      RW29R   

     

    (2) For a given output from step1, I am trying to select field1 and field2 if their field 3 and field 4 matches with field3 and field 4 values in table2. The output is given below.

     

     

    ABCD      RW11L     

    ABCD      RW11R    

    ABCD      RW29R   

     

    Those three rows are present in TABLE1 and not present in TABLE2 and these three rows as well match their FIELD3, FIELD4 values in TABLE1 and TABLE2

     

    Hope I explained the problem.

  • 7. Re: compare two tables
    Prashant Dabral Explorer
    Currently Being Moderated

    Hi,

     

    Hope i could get somewhere to your exact requirement.

     

    WITH T AS (SELECT * 
                  FROM TAB1 A 
                WHERE NOT EXISTS (SELECT 1 
                                    FROM TAB2 B 
                                   WHERE A.FIELD1=B.FIELD1 
                                     AND A.FIELD2=B.FIELD2))
    SELECT *
    FROM T
    WHERE EXISTS (SELECT 1 
                    FROM TAB2 B 
                   WHERE T.FIELD1=B.FIELD1 
                     AND T.FIELD3=B.FIELD3
                     AND T.FIELD4=B.FIELD4)
    

     

    Output

     

    FIELD1FIELD2FIELD3FIELD4
    ABCDRW11LS15514887W047553920
    ABCDRW11RS15525042W047562394
    ABCDRW29RS15514195W047535189
  • 8. Re: compare two tables
    Stew Ashton Expert
    Currently Being Moderated

    First of all, there is a FAQ that explains how to ask questions on this forum: Re: 2. How do I ask a question on the forums?

     

    One thing to do is provide CREATE TABLE and INSERT statements so we can test our solutions. For example:

     

    create table table1(field1, field2, field3, field4) as select

    'ABCD', 'RW11L', 'S15514887', 'W047553920' from dual union all select

    'ABCD', 'RW11R', 'S15525042', 'W047562394' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29R', 'S15514195', 'W047535189' from dual;

     

    create table table2(field1, field2, field3, field4) as select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW11C', 'S15514887', 'W047553920' from dual union all select

    'ABCD', 'RW11', 'S15525042', 'W047562394' from dual union all select

    'ABCD', 'RW29C', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29', 'S15514195', 'W047535189' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW11C', 'S15514887', 'W047553920' from dual union all select

    'ABCD', 'RW11', 'S15525042', 'W047562394' from dual union all select

    'ABCD', 'RW29C', 'S15524330', 'W047543328' from dual union all select

    'ABCD', 'RW29', 'S15514195', 'W047535189' from dual union all select

    'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual;

     

    Next, try to express the requirement without procedural language. When you say "first", "then", "loop" and such you are thinking in procedural code and not in sets. For SQL try to think in sets. If I understand your requirement, here is a solution:

     

    select * from table1

    where (field1, field2) not in (

      select field1, field2 from table2

    )

    and (field1, field3, field4) in (

      select field1, field3, field4 from table2

    );

     

     

     

    FIELD1FIELD2FIELD3FIELD4
    ABCDRW11LS15514887W047553920
    ABCDRW11RS15525042W047562394
    ABCDRW29RS15514195W047535189

     

    Warning: this solution will only work if field1 and field2 are NOT NULL! If they can be null, we need to replace NOT IN by WHERE NOT EXISTS.

  • 9. Re: compare two tables
    don123 Newbie
    Currently Being Moderated

    Hi Prashant, Thanks, It works fine.

     

    Can you please explain SELECT 1 ?

    Does 1 in SELECT statement represents first field in a table or it has different meaning ?

  • 10. Re: compare two tables
    AnnPricks E Guru
    Currently Being Moderated

    Then try this.. It will give correct result as you expected

    SELECT field1,

           field2

    FROM table1 t1

    WHERE NOT EXISTS(SELECT 1

                     FROM table2 t2

                     WHERE t1.field2 = t2.field2)

    AND EXISTS (SELECT 1

                FROM table2 t2

                WHERE t1.field3 = t2.field3

                AND t1.field4 = t2.field4);


    OUTPUT:-

    ------

    FIEL FIELD

    ---- -----

    ABCD RW11R

    ABCD RW29R

    ABCD RW11L

     

  • 11. Re: compare two tables
    don123 Newbie
    Currently Being Moderated

    Hi Stew, Thanks for your suggestions and SQL query. It works fine and I have tested the result.

     

    I have one question in WHERE clause.

    I understood first condition, But in second condition, there is no field2, Kindly explain.

     

    where (field1, field2) not in (

      select field1, field2 from table2

    )

    and (field1, field3, field4) in (

      select field1, field3, field4 from table2

    );

     


  • 12. Re: compare two tables
    Prashant Dabral Explorer
    Currently Being Moderated

    Hi,

     

    When using the EXISTS keyword you need to have a sub-select statement, and only

    the existence of a row is checked, the contents of the row do not matter.

    SELECT is a keyword that controls what is inside the columns that are returned.

    SELECTing 1 or any other column will return the same number of rows.

     

    Also, If your problem is solved then mark thread as answered so that people can concentrate on others unresolved threads.

     

    Regards,

    Prashant Dabral

  • 13. Re: compare two tables
    Stew Ashton Expert
    Currently Being Moderated

    don123 wrote:

    in second condition, there is no field2, Kindly explain.

     

    where (field1, field2) not in (

      select field1, field2 from table2

    )

    and (field1, field3, field4) in (

      select field1, field3, field4 from table2

    );

     


     

    There is no field2 in your second condition either. Why do you think there should be?

     

    Remember, there are two conditions connected by AND: both conditions have to be true.

     

    Come to think of it, there is no reason to have field1 in the first condition! Logically, your requirement could simply be: rows in table1 that are also in table2, except field2 has to be different.

     

    select * from table1

    where field2 not in (

      select field2 from table2

    )

    and (field1, field3, field4) in (

      select field1, field3, field4 from table2

    );

Legend

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