CODEWITHSUNDEEP
postgresql
user2926497
user2926497

Reputation: 535

Postgres NOT IN does not work as expected

I am trying to add the below condition in my query to filter data.

SELECT * 
FROM dump
WHERE letpos NOT IN ('0', '(!)','NA','N/A') ;

I need only records with id 1,2,3 and 6. But the query does not return ids 3 and 6. I get only 1,2.

TABLE:

id name letpos num
1 AAA A 60
2 BBB B
3 CCC 50
4 DDD 0
5 EEE (!) 70
6 FFF 70

I am not sure what is missing? Could anyone advise on how to resolve this?

-Thanks

Upvotes: 2

Views: 4943

Answers (1)

forpas
forpas

Reputation: 164204

In the row with id = 3 the value of letpos is (I suspect) NULL, so the boolean expression in the WHERE clause is:

WHERE NULL NOT IN ('0', '(!)','NA','N/A');

The comparison of NULL with operators like IN, NOT IN, =, > etc always returns NULL and is never TRUE.
So you don't get this row in the results.

Check for NULL also in the WHERE clause:

SELECT * 
FROM dump 
WHERE letpos IS NULL
   OR letpos NOT IN ('0', '(!)', 'NA', 'N/A');

Upvotes: 6

Related Questions