I apologize for the wide distribution but we recently ran into an interesting behaviour using PostgreSQL 8.0.3 and did not know whether this was a bug or intended behaviour.
When an IN clause contains a NULL value the entire in clause is considered as being false, thus no records are returned.
Why doesn't IN evaluate NULL as a value?
so for example:
SELECT count(*) FROM test WHERE key NOT IN ('something'); returns the count of rows...
where SELECT count(*) FROM test WHERE key NOT IN ('something', NULL); does not. table test does not have any NULL values in the key column.
the query plans follow...
mazu=# EXPLAIN ANALYZE SELECT count(*) FROM test WHERE key NOT IN ('something'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=100000022.44..100000022.44 rows=1 width=0) (actual time=0.664..0.665 rows=1 loops=1) -> Seq Scan on test (cost=100000000.00..100000020.38 rows=826 width=0) (actual time=0.030..0.349 rows=168 loops=1) Filter: (("key")::text <> 'something'::text) Total runtime: 0.826 ms (4 rows)
mazu=# EXPLAIN ANALYZE SELECT count(*) FROM test WHERE key NOT IN ('something', NULL); QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=100000022.44..100000022.44 rows=1 width=0) (actual time=0.027..0.029 rows=1 loops=1) -> Result (cost=100000000.00..100000020.38 rows=826 width=0) (actual time=0.002..0.002 rows=0 loops=1) One-Time Filter: NULL::boolean -> Seq Scan on test (cost=100000000.00..100000020.38 rows=826 width=0) (never executed) Filter: (("key")::text <> 'something'::text) Total runtime: 0.110 ms (6 rows)
