MINUS Operator in oracle
oracle, oracle10g, sql
Solution
MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.
In your example, tableA has 389 rows, and tableB has 217 rows; your INTERSECT shows there are no rows in common, which means tableA MINUS tableB is 389 rows (i.e. all of them).
tableB MINUS tableA returns the distinct set of rows in tableB, therefore there are 89 distinct values in tableB.
Problem
MINUS Operator 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 please explain why does the last query return 89 and not 217?