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

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 quering

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

CodePudding user response：

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';