Search code examples
sqlsql-server-2014

SQL NOT IN function not returning expected result

Total number of records in table i1450:

enter image description here

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

enter image description here

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

enter image description here

Both of columns are of varchar type.

Solution

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