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

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

Any NULL values in that column? You might want to try where sold_dealer_id != 36 or sol_dealer_id is null- or simplified towhere sold_dealer_id is distinct from 36` — , Nov 11 '19 at 16:22
all the remaining values are NULL, i just updated one row to have value '36' — Sijan Shrestha, Nov 11 '19 at 16:25
Using IS NULL solves the problem, but I am trying to get values that are not != 36, any suggestion is much appreciated, but i now know the cause is NULL Thanks :) — Sijan Shrestha, Nov 11 '19 at 16:32
Thanks a lot select * from myi_corporate where sold_dealer_id is distinct from 36 got the query worrking :) — Sijan Shrestha, Nov 11 '19 at 16:41
Remember that SQL has tristate logic: true, false, and null. Use is null or is distinct from when comparing against null. — Colin 't Hart, Nov 11 '19 at 18:25

Laurenz Albe · Answer 1 · 2019-11-11T16:50:34.447

7

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.

edited Nov 11 '19 at 16:50

answered Nov 11 '19 at 16:28

Laurenz Albe

51,298
4
39
69

Thanks for pointing this out! Yes the comparison is with NULL = 36 currently and seems fair, is there a correct way to compare with NULL as my query depends on using an id and want to fetch other rows that are sold_dealer_id != <NUMBERr> – Sijan Shrestha Nov 11 '19 at 16:35
I have added a way to do that to the answer. – Laurenz Albe Nov 11 '19 at 16:50
You can also add or sold_dealer IS NULL. – eckes Nov 11 '19 at 18:10

score 2 · Answer 2 · answered Nov 11 '19 at 16:51

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;

score -3 · Answer 3 · answered Nov 11 '19 at 16:07

-3

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

answered Nov 11 '19 at 16:07

eagle275

684
5
6

I tried that, did not work! Thanks though :). I am getting 195 result for a select query but no luck with NOT or != – Sijan Shrestha Nov 11 '19 at 16:10
FYI, it is a foriegn key, does it make a difference ? – Sijan Shrestha Nov 11 '19 at 16:13
Okay turns out something I am doing is wrong, I tried with a string field example select * from myi_corporate where NOT (company_reg_id = '9990000000000'); which seems to work but for that field which is an integer and a foreign key its not working , any solutions? – Sijan Shrestha Nov 11 '19 at 16:19
5

Although the SQL standard defines <> as the "not equals" operator, Postgres does support using != as well. Changing the condition to a negated equals condition won't change a thing. NOT (sold_dealer_id = 36) is the same as sold_dealer_id <> 36 which is the same as (sold_dealer_id != 36 – Nov 11 '19 at 16:20

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

3 Answers3