This discussion is archived
9 Replies Latest reply: Jan 30, 2013 11:31 PM by Manik RSS

Table field comparison

shabar Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Further if there are NULL values in both Col1 and Col2 then what will happen


    Cheers

    Shabar
  • 6. Re: Table field comparison
    shabar Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    NOT IN vs MINUS ---- I see MINUS faster.

    Cheers,
    Manik.