PostgreSQL: WHERE IN and NOT WHERE IN

Question

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 1

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.

Answer 2

This query tells us that table B has total of 3599 rows:

SELECT COUNT(b.id)
FROM B b

Next query tells us that every single one id from B was used in A:

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

Further, this query tells is that table B has 1899 id's that are mentioned in table A:

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

The only possible explanation to this is that some B.id were used in table A as A.id_b more than once per row. Running this query will show list of all duplicate mentions of id_b in table A:

SELECT a.id_b
FROM A a
GROUP BY a.id_b
HAVING count(a.id_b) > 1