CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Dr. Debasish Jana
Dr. Debasish Jana

Reputation: 7118

SQL MINUS showing no difference between first and second while shows difference between second and first

SQL MINUS is used as:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

In case I see no difference between first minus second but see a difference between second minus first, what does this signify? Is there any real difference? If so, then why and how may I get first minus second as no difference? Please help.

Upvotes: 0

Views: 2797

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You can refer the docs to understand the MINUS operator:

The Oracle MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

So if all the records of table1 exist in table2 then there will not be any record shown in the output. But when you reverse the tables and if there is a change in records then the same difference of records can be seen.

enter image description here

Upvotes: 4

Hawk
Hawk

Reputation: 5170

If I understand you correctly, there are two cases of the problem as follows:

  1. Table1 and Table2 have ame number of rows, but different values: In this case using Table1 Minus Table2 will have the same results as Table2 Minuse table1.

  2. Different number of rows: In this case, Table1 Minus Table2 will only return those rows exists in Table1 and do not exist in Table2. If you want to return rows exist in Table2, and do not exist in Table1, you have to write Table2 Minus Table1.

If you want to return all the differences, then you can use UNION ALL: 

Table1 MINUS Table2
UNION All
Table2 MINUS Table1

Upvotes: 1

user5992977
user5992977

Reputation:

Of course there is, MINUS is substractring all the records from the first table, that appear on the second table.

Take this example:

TABLE1:

ID
 1
 2
 4

Table2:

ID
 1
 2
 4
 5

SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2

Will return nothing, since 1,2,4(all of table1 records) appear on table2 although they don't have exactly the same content.

As oppose to:

SELECT * FROM TABLE2
MINUS
SELECT * FROM TABLE1

Will return 5, because its the only value that doesn't appear on table1

So even if you are selecting from the same table, if you select different content(different where conditions) then minus won't work both sides equally .

Upvotes: 2

Related Questions