PostgreSQL: WHERE IN and NOT WHERE IN

postgresql where-in

devdRew · Oct 15, 2012 · Viewed 16.7k times · Source

I have two tables A and B, A referred to B by field A.id_b B.id, so that any id_b value present in B.id.

I have three queries: First one:

SELECT COUNT(b.id)
FROM B b
WHERE b.id NOT IN (
    SELECT a.id_b
    FROM A a)

It gives me 0;

Second one, difference only in NOT:

SELECT COUNT(b.id)
FROM B b
WHERE b.id IN (
    SELECT a.id_b
    FROM A a)

This query gives me: 1899

Third one:

SELECT COUNT(b.id)
FROM B b

And this query gives me 3599

SAME result in:

SELECT a.id_b
FROM A a

guaranty me spliting B.id on two sets, and count of elements in both sets must much total elements count, but I have: 1899 + 0 != 3599.

How that could be?

Answer

Found the reason. There was records in A, which has NULL values in A.id_b. That's why query:

SELECT COUNT(b.id)
FROM B b
WHERE b.id NOT IN (
    SELECT a.id_b
    FROM A a)

was returning 0.