Why is != not working? how to compare with null values? [duplicate]

Posted on

Question :

I am having a hard time understanding why the following query is not working?

select id,sold_dealer_id from myi_corporate where sold_dealer_id != 36;

When I do the following I am getting the result

select id,sold_dealer_id from myi_corporate where sold_dealer_id = 36;

However for != I am not getting the opposite results.

I am returning to psql after a long time after using MongoDB and might have forgotten the basics. Any suggestion is much appreciated

Answer :

You can use != rather than the SQL standard operator <>, but I recommend using the latter. That has no influence on your problem though.

Your table must contain some NULL values in sold_dealer_id.

Now NULL = 36 is not true, but NULL <> 36 is also not true, so such lines are excluded from both query results.

You can use sold_dealer_id IS DISTINCT FROM 36 to get the opposite of sold_dealer_id = 36. That operator will treat NULL values as if they were normal values.

You are getting the results because a null value will never match anything else. If you know you are going to have null values I would also exclude those from the result.

select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id = 36
and    sold_dealer_id is not null;


select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id != 36
and    sold_dealer_id is not null;

You can also do a quick search to find any with a null value

select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id is null;

By using correct postgres-Syntax…

select id,sold_dealer_id from myi_corporate where NOT (sold_dealer_id = 36);

Postgres is a database, not a c-family programming language

https://www.techonthenet.com/postgresql/not.php with some examples

Leave a Reply

Your email address will not be published. Required fields are marked *