1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need Help in Oracle Query!!

Discussion in 'SQL PL/SQL' started by lahari gutlapalli, Mar 22, 2012.

  1. Hi,

    I need help in an oracle query .

    I have a table lets say table1 with a column name data. and another table lets say table2 have same column name. both columns stores same type of data. both tables has an id column which can allow me to inner join table1 and table2.

    Now I'm trying to write a query to get count of rows with condition table.data <> table2.data.

    how can I achive this?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I know you mentioned the join but you do need to include it in the conditions otherwise someone will give you a query that produces the wrong result:

    Code (SQL):
     
    SQL> CREATE TABLE tab1(
      2          id      NUMBER,
      3          DATA    varchar2(20));
    TABLE created.
    SQL>
    SQL> CREATE TABLE tab2(
      2          id      NUMBER,
      3          DATA    varchar2(20));
    TABLE created.
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO tab1
      4                  VALUES(i, 'This is record '||i);
      5                  IF MOD(i,11) = 0 THEN
      6                          INSERT INTO tab2
      7                          VALUES(i, 'Wombat '||i);
      8                  ELSE
      9                          INSERT INTO tab2
     10                          VALUES(i, 'This is record '||i);
     11                  END IF;
     12
     13          END loop;
     14
     15          commit;
     16
     17  END;
     18  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT COUNT(*)
      2  FROM tab1, tab2
      3  WHERE tab2.DATA <> tab1.DATA;
           COUNT(*)
    ---------------
             999090
    SQL>

     
    IF done properly:

    Code (SQL):
     
    SQL> SELECT COUNT(*)
      2  FROM tab1 JOIN tab2 ON (tab2.id = tab1.id)
      3  WHERE tab2.DATA <> tab1.DATA;
           COUNT(*)
    ---------------
                 90
    SQL>
     
     
    Bharat likes this.