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

    compare two tables

    don123

      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
          Turion

          Hi,

           

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

           

          Regards,

          Prashant

          • 2. Re: compare two tables
            don123

            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
              Turion

              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

                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

                  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

                    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
                      Turion

                      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

                        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

                          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

                            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

                              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
                                Turion

                                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

                                  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

                                  );