Oracle “NOT IN” not returning correct result?

I’m comparing two tables that share unique values between each other using NOT IN function in Oracle but I’m getting

select count(distinct CHARGING_ID)   from BILLINGDB201908 where CDR_TYPE='GPRSO'

​x
 
select count(distinct CHARGING_ID)   from BILLINGDB201908 where CDR_TYPE='GPRSO'​

the output is: 521254 for all charging ids –< this is the total unique charging ID’s in BILLINGDB201908

Now I want to find id’s in table BILLINGDB201908 that also exist in table CBS_CHRG_ID_AUG

select count(distinct CHARGING_ID)   from BILLINGDB201908 where CDR_TYPE='GPRSO'
AND charging_id IN (select CHARGINGID from CBS_CHRG_ID_AUG);

 
select count(distinct CHARGING_ID)   from BILLINGDB201908 where CDR_TYPE='GPRSO'AND charging_id IN (select CHARGINGID from CBS_CHRG_ID_AUG);​

— the result back315567 charging ID exist BILLINGDB201908 and also exist in CBS_CHRG_ID_AUG

Now I want to find charging ids that not exist in CBS_CHRG_ID_AUG but exist BILLINGDB201908

select count(distinct CHARGING_ID)   from prmdb.CDR_TAPIN_201908@prmdb where CDR_TYPE='GPRSO'
AND charging_id NOT IN (select CHARGINGID from CBS_CHRG_ID_AUG);

 
select count(distinct CHARGING_ID)   from prmdb.CDR_TAPIN_201908@prmdb where CDR_TYPE='GPRSO'AND charging_id NOT IN (select CHARGINGID from CBS_CHRG_ID_AUG);​

–the result back 0 !? I should get 205687 exactly because 521254-315567 = 205687 ?

Answer

The missing record is having null value CHARGINGID.

Please try doing select where CHARGINGID is null vs is not null

Advertisement

Answer