CODEWITHSUNDEEP
postgresql
I am not Fat
I am not Fat

Reputation: 447

How to select rows where the condition where all rows are being extracted for a given condition?

I have this table

CREATE TABLE fruits 
(
    id SERIAL,
    name VARCHAR
);

with these entries

INSERT INTO fruits(name) 
VALUES('Orange');
INSERT INTO fruits(name) 
VALUES('Ananas');
INSERT INTO fruits(name) 
VALUES(null);

When I try to to select all rows that not equal to 'Ananas' by querying

select * 
from fruits 
where name <> 'Ananas'

I get these rows:

id  name
-----------
1   Orange

What I would have expected was this

id  name
-----------
1   Orange
3   null

How do I ensure that all rows that fulfills the condition gets selected?

Example in dbfiddle: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=a963d39df0466701b0a96b20db8461e6

Upvotes: 1

Views: 38

Answers (1)

user330315
user330315

Reputation:

Any "normal" comparison with null yields "unknown" which is treated as false in the context of the WHERE clause.

You need to use the null safe operator is distinct from:

select *
from fruits
where name is distinct from 'Ananas';

Alternatively you could convert NULL values to something different:

select *
from fruits
where coalesce(name, '') <> 'Ananas';

Upvotes: 3

Related Questions