CODEWITHSUNDEEP
sqloracle-databaseplsql
Beefstu
Beefstu

Reputation: 857

Oracle capture count of minus operation

I have 2 tables (TABLE_A & TABLE_B) where I'm using the MINUS command to see if there are differences in the tables.

In my example below you can see that TABLE_A has an additional row.

Is there a way to capture the numeric difference between the two tables, in this case 1 row.

If there is a difference >0 then display the value. Although my example is small it could contain many rows. Therefore I would only like to do the MINUS command once if possible. I'm also also amenable to alternative solutions and not tied to the MINUS command or if this can be done with SQL only that will work too.

Thanks in advance for your expertise and all who answer.


CREATE TABLE TABLE_A(
 seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
nm VARCHAR(30)
);
/

CREATE TABLE TABLE_B(
seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
 nm VARCHAR(30)
);
/

BEGIN
 FOR I IN 1..4 LOOP
  INSERT INTO TABLE_A (nm) VALUES('Name '||I);
end loop;

FOR I IN 1..3 LOOP
  INSERT INTO TABLE_B (nm) VALUES('Name '||I);
end loop;

 END;
 
-- MINUS operation

SELECT nm FROM TABLE_A
MINUS
SELECT nm FROM  TABLE_B;

Output:
NM
Name 4

Pseudo code

Do minus command

If difference >0 then display rows

Upvotes: 0

Views: 437

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

If I understood the question correctly you can do it using analytic count:

select *
from (
   select v.*,count(*)over() cnt
   from (
      SELECT nm FROM TABLE_A
      MINUS
      SELECT nm FROM  TABLE_B
   ) v
)
where cnt>=4;

DBFiddle: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=0ac62f3d1ea835f60427a1da8efb965e

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

There are many ways for this, you can try 1 as below -

SELECT COUNT(*)
  FROM (SELECT nm FROM TABLE_A
         MINUS
        SELECT nm FROM TABLE_B);

Another method maybe -

SELECT COUNT(*)
  FROM TABLE_A A
 WHERE NOT EXISTS (SELECT NULL
                     FROM TABLE_B B
                    WHERE A.nm = B.nm)

Upvotes: 1

Related Questions