CODEWITHSUNDEEP
sqlpostgresql
priyank
priyank

Reputation: 4724

postgres column with nulll values and filtering with "!="

Here is my query:

select * from students where status != 4.

I am not getting rows with status null.

Shouldn't it return all the rows where status is not 4 - including rows with null status values?

I can get what I need by following query.

select * from students where status != 4 or status is null

Upvotes: 0

Views: 93

Answers (4)

Bohemian
Bohemian

Reputation: 425198

Any comparison using normal comparison operators with null is never true, except for the two special IS NULL and IS NOT NULL comparisons.

Upvotes: 0

user2478690
user2478690

Reputation: 441

Use:
IS DISTINCT FROM 

SELECT * FROM students WHERE status IS DISTINCT FROM 4;

http://www.postgresql.org/docs/current/interactive/functions-comparison.html

Upvotes: 4

C.Champagne
C.Champagne

Reputation: 5489

This is a bit disturbing but null is not a value and requires its specific operator is.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221106

SQL's understanding of NULL is "three-valued". Think of NULL as UNKNOWN. So, in other words:

UNKNOWN  = 4 yields      UNKNOWN
UNKNOWN != 4 yields also UNKNOWN

In other words, if a student has status is null, then it is "unknown" whether that status is different from 4

Upvotes: 2

Related Questions