Query to check all the records in Table A is available and matching with Table B-CodePudding

I have two tables Table A(Product,loc) and Table B(Product,Loc).i need to check whether all the records in Table A is available and matching with Table B.

Table A -- 8615416 records Table B--8999626 records.

When i tried this using exists am not getting the exact records

select Prd, loc
from hist a
where exists (select prd, loc from _hist_stg b
              where a.prd = b.prd and a.loc = b.loc);--930514

CodePudding user response：

You could try adapting this.


create table T pctfree 0 as
    select rownum x, rownum y, rownum z
   from
     ( Select 1 from dual connect by level <= 100),
      ( Select 1 from dual connect by level <= 100)
 /

 create table T1 pctfree 0 as
    select rownum x, rownum y, rownum z
    from
      ( Select 1 from dual connect by level <= 100),
      ( Select 1 from dual connect by level <= 99)
    /

select count(*)   from t, t1
 where t.x = t1.x( )
  and t1.x is null
  /

 insert /*  APPEND */ into T1
   select t.x, t.y, t.z
  from t, t1
    where t.x = t1.x( )
    and t1.x is null
  /

CodePudding user response：

minus set operator shows values missing in _hist_stg (ideally, that should return an empty set which means that all rows from hist exist in _hist_stg):

select prd, loc from hist 
minus
select prd, loc from _hist_stg;

If you want to see matching values, intersect them (result should return all rows from hist which means that all of them are contained in _hist_stg):

select prd, loc from hist 
intersect
select prd, loc from _hist_stg;