Identify Non matching records in Join- Oracle

oracle

I have a Lookup table like below

Table MASTER

CD SEQ DEF
1 1 A
1 1 B
1 1 C
2 1 A
2 1 B
2 1 C
2 1 D

Table INFO

SID CD SEQ DEF
1000 2 1 A
1000 2 1 B
1000 2 1 C

2000 2 1 B
2000 2 1 C
2000 2 1 D
2000 2 1 E

How should i write a query to say for the SID 1000 using the same CD, and SEQ
D is missing compared to Master table.
and for the SID 2000 using the same CD,SEQ All master DEF records exists and also extra DEF record E exists which is not there in Master table.

Your help is highly appreciated. Thanks

Best Answer

Assuming that the combinations of values in CD, SEQ, DEF in the MASTER table are unique, you could write a cross join which gives you all "valid" combinations for the INFO table - see Query1. Then you could this query, along with a query that returns all rows from INFO, and the set operators MINUS and UNION ALL to find the rows you are looking for (see Query2).

Query1

select sid, cd, seq, def
from 
  ( select unique sid from info )
, ( select cd, seq, def from master )
;

SID CD  SEQ DEF
1000    1   1   A    
1000    1   1   B    
1000    1   1   C    
1000    2   1   A
1000    2   1   B
1000    2   1   C
1000    2   1   D
2000    1   1   A    
2000    1   1   B    
2000    1   1   C    
2000    2   1   A
2000    2   1   B
2000    2   1   C
2000    2   1   D

Query2

select *
from (
  (
    select sid, cd, seq, def, 'missing from info' as comments     -- Query1
    from 
      ( select unique sid from info )
    , ( select cd, seq, def from master )
    minus
    select sid, cd, seq, def, 'missing from info' from info       -- INFO + comment 
  ) union all (
    select sid, cd, seq, def, 'in info (not in master)' from info -- INFO + comment
    minus
    select sid, cd, seq, def, 'in info (not in master)'           -- Query1
    from 
      ( select unique sid from info )
    , ( select cd, seq, def from master )
  )
) where cd = 2 and seq = 1 
;

Result

SID   CD  SEQ DEF  COMMENTS
1000  2   1   D    missing from info
2000  2   1   A    missing from info
2000  2   1   E    in info (not in master)

Caveat: In your question, you are saying (among other things) that "SID 2000 using the same CD,SEQ All master DEF records exist" although the combination (cd, seq, def) 2-1-A is missing from info. Query2 will report this combination to be missing.

Dbfiddle here. Table comparison technique described here.

Related Question