9 Replies Latest reply: Jan 31, 2013 1:31 AM by Manik RSS

    Table field comparison

    shabar
      Hi All


      I ran following query for field comparison in Tab1 and Tab2. Needs to check what are the Col1 values missing in
      Tab2


      SELECT Tab1.*
      FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
      WHERE Tab2.Col2 Is Null;


      Here count is 16177

      When I run same query with NOT NULL as follows

      SELECT Tab1.*
      FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
      WHERE Tab2.Col2 Is Null;

      I got count as 3190200


      But total of above two should tally to Tab1 total row count (i.e. 17683 as follows)


      But When I check Tables row count seperately its as follws

      Tab1     17683
      Tab2     1642488



      What could be the issue


      Cheers

      Shabar
        • 1. Re: Table field comparison
          Manik
          shabar wrote:
          Hi All


          I ran following query for field comparison in Tab1 and Tab2. Needs to check what are the Col1 values missing in
          Tab2


          SELECT Tab1.*
          FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
          WHERE Tab2.Col2 Is Null;


          Here count is 16177

          When I run same query with NOT NULL as follows

          SELECT Tab1.*
          FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
          WHERE Tab2.Col2 Is Null;

          I got count as 3190200


          But total of above two should tally to Tab1 total row count (i.e. 17683 as follows)


          But When I check Tables row count seperately its as follws

          Tab1     17683
          Tab2     1642488



          What could be the issue


          Cheers

          Shabar
          WHere is not null condition query???

          Cheers,
          Manik.
          • 2. Re: Table field comparison
            shabar
            Sorry it was a mistake...

            Second Query should read as

            SELECT Tab1.*
            FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
            WHERE Tab2.Col2 Is Not Null;

            Then it gives 3190200


            Cheers

            Shabar
            • 3. Re: Table field comparison
              Manik
              I cannot test this as I dont have data.

              Please post the o/ps of the following queries:
              ---
              SELECT COUNT (1)
                FROM (SELECT Tab1.*
                        FROM Tab1 JOIN Tab2 ON Tab1.Col1 = Tab2.Col2);
              
              SELECT COUNT (1)
                FROM (SELECT Tab1.*, Tab2.*
                        FROM Tab1 LEFT JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
               WHERE Col2 IS NULL;
              
              SELECT COUNT (1)
                FROM (SELECT Tab1.*, Tab2.*
                        FROM Tab1 LEFT JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
               WHERE Col2 IS NOT NULL;
              
              SELECT COUNT (1)
                FROM (SELECT Tab1.*
                        FROM Tab1 FULL OUTER JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
              where col2 is not null;
              Also please post the explain plan of your not null query.. Also confirm if there are any duplicates in the table.

              Cheers,
              Manik.
              • 4. Re: Table field comparison
                shabar
                Hi Manik

                Thanks for your reply


                SELECT COUNT (1)
                FROM (SELECT Tab1.*
                FROM Tab1 JOIN Tab2 ON Tab1.Col1 = Tab2.Col2);

                Count - 3190200


                SELECT COUNT (1)
                FROM (SELECT Tab1.*, Tab2.*
                FROM Tab1 LEFT JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
                WHERE Col2 IS NULL;

                Count - 16177


                SELECT COUNT (1)
                FROM (SELECT Tab1.*, Tab2.*
                FROM Tab1 LEFT JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
                WHERE Col2 IS NOT NULL;

                Count - 3190200

                SELECT COUNT (1)
                FROM (SELECT Tab1.*
                FROM Tab1 FULL OUTER JOIN Tab2 ON Tab1.Col1 = Tab2.Col2)
                where col2 is not null;


                Count - 7807656

                Also please post the explain plan of your not null query.. Also confirm if there are any duplicates in the table.


                Yes there are duplicates in both Tab1.Col1 and Tab2.Col2




                Cheers

                Shabar
                • 5. Re: Table field comparison
                  shabar
                  Further if there are NULL values in both Col1 and Col2 then what will happen


                  Cheers

                  Shabar
                  • 6. Re: Table field comparison
                    shabar
                    Hi Manik

                    I found 593 NULL values in Tab1.Col1. Will this effect to the row count



                    Cheers

                    Shabr
                    • 7. Re: Table field comparison
                      Manik
                      You can try it yourself,... Whatever I say would be like a guess because I dont have the data..
                      Try this query.
                      SELECT COUNT (1)
                        FROM (SELECT DISTINCT Tab1.*, Tab2.*
                                FROM    (SELECT *
                                           FROM Tab1
                                          WHERE col1 IS NOT NULL)
                                     LEFT JOIN
                                        (SELECT *
                                           FROM Tab2
                                          WHERE col2 IS NOT NULL)
                                     ON Tab1.Col1 = Tab2.Col2)
                       WHERE Col2 IS NOT NULL;
                      Also if your intention is to compare the column
                      use minus and intersect -- Use your table here.. provided you have same number of columns and same datatype...
                      Just as a template check this.
                      SELECT *
                        FROM (SELECT a.*, 'Present in t1 and absent in t2' cmmnt
                                FROM t1 a
                              MINUS
                              SELECT b.*, 'Present in t1 and absent in t2'
                                FROM t2 b)
                      UNION ALL
                      SELECT *
                        FROM (SELECT a.*, 'Present in t2 and absent in t1'
                                FROM t2 a
                              MINUS
                              SELECT b.*, 'Present in t2 and absent in t1'
                                FROM t1 b)
                      UNION ALL
                      SELECT *
                        FROM (SELECT a.*, 'Present in both tables'
                                FROM t2 a
                              INTERSECT
                              SELECT b.*, 'Present in both tables'
                                FROM t1 b);
                      Cheers,
                      Manik.
                      • 8. Re: Table field comparison
                        Gurujothi
                        Hi shabar,

                        As you mentioned Table1.col1=Table2.col2,
                        now you want to find the Table1.col1 which is missed in Table2.col2 right?

                        Let assume,
                        CREATE TABLE table1(col1 NUMBER,col22 NUMBER);
                        
                        CREATE TABLE table2(col1 NUMBER,col2 NUMBER);
                        
                        INSERT INTO table1
                             VALUES (1, 2);
                        INSERT INTO table1
                             VALUES (2, 2);
                        INSERT INTO table1
                             VALUES (NULL, 3);
                        INSERT INTO table1
                             VALUES (4, 4);
                        
                        INSERT INTO table2
                             VALUES (1, 1);
                        INSERT INTO table2
                             VALUES (2, 2);
                        INSERT INTO table2
                             VALUES (3, NULL);
                        INSERT INTO table2
                             VALUES (4, 3);
                        Select * from Table1;
                        
                        Col1       col2
                        1            2
                        2            2
                                      3
                        4            4
                        and
                        Select * from Table2
                        Col1       col2
                        1            1
                        2            2
                        3            
                        4            3
                        Here Table1.col1 value 4 is missing in Table2.col2,
                        so you can chek like,
                        SELECT col1
                          FROM table1
                         WHERE col1 NOT IN (SELECT col2
                                              FROM table2
                                             WHERE col2 IS NOT NULL)
                        
                        
                        COL1
                        ====
                        4
                        Hope this helps you.


                        Regrads,
                        Guru
                        • 9. Re: Table field comparison
                          Manik
                          NOT IN vs MINUS ---- I see MINUS faster.

                          Cheers,
                          Manik.