The sql query returns zero when I use NOT IN operator

Question

There are two tables

A(ESN_NO,MAKER, HANDSET, MODEL, FLAG, OFFER, STATUS, STATUS_UPDATEd_date) ,             
B(ESN_NO,MAKER, HANDSET, MODEL, FLAG, OFFER, STATUS, STATUS_UPDATEd_date)

I need to add data from the bau_load table into the omh_esn_model_details_new table without duplicates.

I added 80,000 rows into the A table.

Then to see the common esn_no I used an IN operator; this is the query:

select count(*) 
from A 
where esn_no in (select esn_no from B);

count came as 74,000--- so there are around 6000 new esn_no

But when I used a NOT IN operator:

select count(*) 
from B 
where esn_no not in (select esn_no from A);

count came as 0

select count(*) 
from A 
where esn_no not in (select esn_no from B);

count came as 0

and when i used this query

select count(*) 
from A 
where esn_no not in (select esn_no 
                     from A 
                     where esn_no in (select esn_no from B));

count came as 6000

So, I want to know why I did not return 6000 when I used the NOT IN operator?

They are likely `NULL` values. – Siyual Aug 05 '16 at 14:37 — Siyual, Aug 05 '16 at 14:37
can you explain it? please – Pavan Kumar Munnam Aug 05 '16 at 14:53 — Pavan Kumar Munnam, Aug 05 '16 at 14:53
use `NVL` where needed to replace those NULL values! – JT4U Aug 05 '16 at 15:08 — JT4U, Aug 05 '16 at 15:08

The sql query returns zero when I use NOT IN operator

0 Answers0