CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Vishal5364
Vishal5364

Reputation: 293

Minus in Oracle

I have a table SVS_TEMP which contains all the customers as

ACCTID              CUSTID        PATH                        CUSTNAME
9080001380318   20800415729 /web/intsoft/DATA_MIGRATION.tif MAHAO JOHANE
9080001805335   10800101164 /web/intsoft/DATA_MIGRATION.tif LINDIWE RAQECHE
9080001380318   10800292271 /web/intsoft/DATA_MIGRATION.tif Mandate
9080002496026   10800387685 /web/intsoft/DATA_MIGRATION.tif MOEKETSI D. NTOI
9080003455730   10800618738 /web/intsoft/DATA_MIGRATION.tif Mandate

There is no primary key in this table

Now another table is signotherinfo with data as

SIGNID    ACCTID        
1093418 9080001380318
1089652 9080001380318
1089711 9080001380318
1089910 9080001380318
1127222 9080001380318
1108463 9080001380318

where signid is primary key

I want to get all the records from SVS_TEMP which are not in signotherinfo

Upvotes: 1

Views: 92

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

An OUTER JOIN of the two tables will do what you want:

SELECT s.*
  FROM SVS_TEMP t
  LEFT OUTER JOIN SIGNOTHERINFO s
    ON s.ACCTID = t.ACCTID
  WHERE s.ACCTID IS NULL;

What this says is "take all the rows in SVS_TEMP and join them with rows in SIGNOTHERINFO which match on the ACCTID field, keeping all rows from SVS_TEMP even if there is no matching row in SIGNOTHERINFO". Then the WHERE clause says "retain only those result set rows for which matching data in SIGNOTHERINFO was not found".

Best of luck.

Upvotes: 1

Hambone
Hambone

Reputation: 16377

Without knowing more about what constraints you might have (you mentioned no PK), I'd say an anti-join would be your best bet:

select *
from SVS_TEMP t
where not exists (
  select null
  from sigotherinfo o
  where t.acctid = o.acctid
)

The main advantage of an anti-join, like a semi-join (exists) is if it finds a match, it "stops looking" so to speak.

Upvotes: 2

Related Questions