CODEWITHSUNDEEP
sqldatabaseoracle-databasesubquery
Osama Al-Banna
Osama Al-Banna

Reputation: 1515

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'

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);

--- 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);

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

Upvotes: 1

Views: 2006

Answers (5)

William Robertson
William Robertson

Reputation: 16001

There are two dangers with not in when the subquery key may contain nulls:

  1. If there actually is a null value, you may not get the result you were expecting (as you have found). The database is actually correct, even though nobody in the history of SQL has ever expected this result.
  2. Even if all key values are populated, if it is possible for the key column to be null (if it is not defined as not null) then the database has to check in case there is a null value, so queries are limited to inefficient row by row filter operations, which can perform disastrously for large volumes. (This was true historically, although these days there is a Null-aware anti-join and so the performance issue may not be so disastrous.)
create table demo (id) as select 1 from dual;

select * from demo;

        ID
----------
         1

create table table_with_nulls (id) as (
  select 2 from dual union all
  select null from dual
);

select * from table_with_nulls;

        ID
----------
         2

select d.id
from   demo d
where  d.id not in
       ( select id from table_with_nulls );

no rows selected

select d.id
from   demo d
where  d.id not in
       ( select id from table_with_nulls
         where  id is not null );

        ID
----------
         1

The reason is that 1 <> null is null, not false. If you substitute a fixed list for the not in subquery, it would be:

select d.id
from   demo d
where  d.id not in (2, null);

which is really the same thing as

select d.id
from   demo d
where  d.id <> 2 and d.id <> null;

Obviously d.id <> null will never be true. This is why your not in query returned no rows.

Upvotes: 0

Shantanu Kher
Shantanu Kher

Reputation: 1054

You can get this list using LEFT OUTER JOIN.

SQL to return list of charging ids that not exist in CBS_CHRG_ID_AUG but exist BILLINGDB201908 -

select count(distinct CHARGING_ID)   
from prmdb.CDR_TAPIN_201908@prmdb a 
left join CBS_CHRG_ID_AUG b on a.CHARGING_ID = b.CHARGINGID 
where a.CDR_TYPE='GPRSO' and b.CHARGINGID is null;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

NOT IN returns no rows if any value from the subquery is NULL. Hence, I strongly, strongly recommend NOT EXISTS:

SELECT count(distinct CHARGING_ID)   
FROM prmdb.CDR_TAPIN_201908@prmdb  ct
WHERE CDR_TYPE = 'GPRSO' AND
      NOT EXISTS (SELECT 1
                  FROM CBS_CHRG_ID_AUG ccia
                  WHERE ccia.charging_id = ct.charging_id
                 );

I also recommend changing your first query to EXISTS. In fact, just don't use IN and NOT IN with subqueries, and you won't have this problem.

Upvotes: 4

GMB
GMB

Reputation: 222462

I would recommend not exists rather than not in; it is null-safe, and usually more efficient:

select count(distinct charging_id)   
from billingdb201908 b
where 
    b.cdr_type = 'gprso'
    and not exists (select 1 from cbs_chrg_id_aug a where a.chargingid = b.chargingid)

Upvotes: 1

Kacper
Kacper

Reputation: 4818

The missing record is having null value CHARGINGID.

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

Upvotes: 1

Related Questions