select * from test;
select * from test where name not in ('amtf');
Why?
2 Answers
As others have said, the problem here is, that you're comparing against a null value, so it returns nothing, because it considers it as false, and I'll go even further that even if you say where name <> 'admf' it wont work, and even if you add more rows it will ignore the null row, and it's not just in PostgreSQL, it doesn't work in SQL-Server or MySQL either.
As you can see in these db<>fiddles SQL-Server, MySQL, and PostgreSQL.
And the reason why it doesn't work is, because you're saying name should not equal a specific value. First name needs to be a value it should not be equal to a value, but when name is null it doesn't have a value, and even more for a side note null itself is not equal null.
The way to solve it is to convert it to a empty string by using COALESCE(name,'') or in SQL-Server you can also use isnull(name,''), and then compare it, or you can add or name is null which will return you all rows, including null, where name <> 'some value'.
Comments
Well the condition is right and the response for that as soo .
select * from test where name not in ('amtf');
your query is saying : give me all the records that the name Column is not in ('amtf').
you have 2 column's on is amtf and the other is null.
amtf will no be brought because of the condition and the other column is null -> no name set
name not in ('amtf')whennameisnullmakes the conditionnullwhich then behaves as false in the where clause (NOTE: That is not the same as being false).select * from test where name not in('amtf') or name is null;