CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Phani
Phani

Reputation: 3315

Postgres "NOT IN" operator usage

In my database when I run the following query, I get 1077 as output.

select count(distinct a_t1) from t1;

Then, when I run this query, I get 459.

select count(distinct a_t1) from t1 
where a_t1 in (select a_t1 from t1 join t2 using (a_t1_t2) where a_t2=0);

The above is the same as, this query which also give 459:

select count(distinct a_t1) from t1 join t2 using (a_t1_t2) where a_t2=0

But when I run this query, I get 0 instead of 618 which I was expecting:

select count(distinct a_t1) from t1 
where a_t1 not in (select a_t1 from t1 join t2 using (a_t1_t2) where a_t2=0);

I am running PostgreSQL 9.1.5, which really might not be necessary. Please point out my mistake in the above query.

UPDATE 1: I created a new table and output the result of the subquery above into that one. Then, I ran a few queries:

select count(distinct a_t1) from t1
where a_t1 not in (select a_t1 from sub_query_table order by a_t1 limit 10);

And Hooray! now I get 10 as the answer! I was able to increase the limit until 450. After that, I started getting 0 again.

UPDATE 2:

The sub_query_table has 459 values in it. Finally, this query gives me the required answer:

select count(distinct a_t1) from t1
where a_t1 not in (select a_t1 from sub_query_table order by a_t1 limit 459);

Where as this one, gives 0 as the answer:

select count(distinct a_t1) from t1
where a_t1 not in (select a_t1 from sub_query_table);

But, why is this happening?

Upvotes: 1

Views: 1675

Answers (1)

OkieOth
OkieOth

Reputation: 3704

The 'NOT IN' operator works only over 'NOT NULL'. Columns with a value of null are not matched.

select count(distinct a_t1) from t1 
where a_t1 not in (select a_t1 from t1 join t2 using (a_t1_t2) where a_t2=0) OR a_t1 IS NULL;

Upvotes: 2

Related Questions