Geek Answers Handbook

MINUS operator in oracle

MINUS statement

I have 2 tables A and B.

SELECT COUNT(*) FROM (SELECT * FROM tableA)

returns 389

 SELECT COUNT(*) FROM (SELECT * FROM tableB)

returns 217

 SELECT COUNT(*) FROM (SELECT * FROM tableA INTERSECT SELECT * FROM tableB)

returns 0

 SELECT COUNT(*) FROM (SELECT * FROM tableA MINUS SELECT * FROM tableB)

returns 389

 SELECT COUNT(*) FROM (SELECT * FROM tableB MINUS SELECT * FROM tableA)

retuns 89

Can someone explain why the last query returns 89, not 217?

+8
sql oracle oracle10g
source share
2 answers

MINUS takes the first result set and removes everything that exists in the second result set; It also removes any duplicates.

In your example, the table has 389 rows, and tableB has 217 rows; your INTERSECT shows that there are no common rows, which means tableA MINUS tableB - 389 rows (i.e. all).

tableB MINUS tableA returns a separate set of rows in tableB, so there are 89 different values ​​in tableB.

+18
source share

Suppose that if you set A and B, A = {1,2,3,4} and count (A) = 4, B = {5,6,6,7,7} and count (B) = 5

AB = {1,2,3,4}, thus counting (AB) = count (A) = 4

But BA = {5,6,7} and count (B) = 3

Thus, we understand that minus eliminates duplicate terms (or strings). This is the reason why the number of rows was reduced from 217 to 89.

Hope this helps.

+10
source share

More articles:


All Articles