Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How Does MINUS Work?

SaurabhKJan 5 2010 — edited Jan 5 2010
Hi,

I want to do a count(*) table 1 minus count(*) table 2 in a test case. How does minus work in functions? 
create table count1( col1 number);
create table count2( col2 number);

truncate table count1;
truncate table count2;
insert into count1 values(1);
insert into count1 values(2);
insert into count1 values(3);
insert into count1 values(4);
insert into count1 values(5);
commit;
insert into count2 values(1);
insert into count2 values(2);
insert into count2 values(3);
insert into count2 values(5);
commit;
SQL> select * from count1
  2  minus
  3  select * from count2;

      COL1
----------
         4

SQL> 
SQL> select count(*) from count1
  2  minus
  3  select count(*) from count2;

  COUNT(*)
----------
         5

SQL>
This post has been answered by Karthick2003 on Jan 5 2010
Jump to Answer

Comments

HuaMin Chen
do you expect to have this?
select count(*) from
(select * from count1
minus
select * from count2);
Sarah_7
Hi,

Minus works on sets of values.
[C1]

a
b
c
d

[C2]
a
b
d

Select C1 from T1
minus
select C2 from T1;

The o/p will be c. It basically removes all common values and displays what is there in first set.

For example, the below query will all return rows that are in table_A, but not in table_B:

SELECT * FROM table_A
MINUS
SELECT * FROM table_B;
Additionally, if there are two identical rows in table_A, and that same row exists in table_B, BOTH rows from table_A will be removed from the result set.

Search more, you will get plenty of examples.

Regds
Sarah
1 person found this helpful
730428 
select * from count1
minus
select * from count2;
Means all rows of count1 not present in count2, that is to say one row (4).
select count(*) from count1
minus
select count(*) from count2;
Means number of rows of count1 if it is different from number of rows of count2, no rows if count1 and count2 contains the same number of rows
select count(*) from (
select * from count1
minus
select * from count2
);
Means number of DISTINCT rows of count1 that are not present in count2 
select count(a.rowid) - count(b.rowid) 
  from count1 a full outer join count2 b on a.rowid=b.rowid;
Means number of rows of count1 - number of rows of count2, where - is arithmetic minus (not minus as defined in set theory).

Try inserting some duplicate values and you'll get it.


Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/02/query-gerarchiche/]
Karthick2003
Answer
Minus works like this. Consider this SQL
<query1> MINUS <query2>
Now what MINUS does is removes records from query1 which occur in query2 and displays the remaining records of query1.

So in your case
select count(*) from count1 -- Result is 5
minus
select count(*) from count2 -- Result is 4
This returns 5 as the count of table count1 is 5.
Marked as Answer by SaurabhK · Sep 27 2020
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 2 2010
Added on Jan 5 2010
4 comments
5,997 views