SQL NOT IN function not returning expected result

Question

Total number of records in table i1450:

Total number with condition where i.BROJ is equal to field REFERENCA in other table:

Shouldn't it return difference between last two results (which is 64) when I use NOT IN in WHERE clause?

Both of columns are of varchar type.

score 1 · Accepted Answer · answered Dec 15 '17 at 14:45

1

If you have any NULL values in the REFERENCA column from the FpsPmtOrderRQ table then the NOT IN clause will not work as expected - (the reason why)

A solution is to remove NULL values from the result returned by the subselect.

SELECT COUNT(*)
FROM i1450 j
WHERE i.BROJ NOT IN (SELECT REFERENCA FROM FpsPmtOrderRQ WHERE REFERENCA IS NOT NULL)

answered Dec 15 '17 at 14:45

Radu Gheorghiu

20,049
16
72
107

Will it count those records in 1st table in `BROJ` where value is `NULL`? – Dec 15 '17 at 14:48

score 0 · Answer 2 · answered Dec 15 '17 at 14:44

0

If the sub-query returns a null value, the IN will not be true. Do NOT EXISTS instead.

select count(*)
from i1450 i
where not exists (select 1 from FpsPmtOrderRQ f
                  where i.broj = f.REFERENCA)

answered Dec 15 '17 at 14:44

jarlh

42,561
8
45
63

score 0 · Answer 3 · answered Dec 15 '17 at 14:45

0

I think you need to coalesce your field to handle nulls. That is probably why you get 0. By doing:

where coalesce(I.BROJ,'n/a') not in (select coalesce(REFERENCA,'')

or something similar, you would exclude nulls, and return a proper count.

answered Dec 15 '17 at 14:45

Dominic

194
4

It's better to simply skip the null rows. (As Radu's answer.) – jarlh Dec 15 '17 at 14:46

SQL NOT IN function not returning expected result

3 Answers3