sql - Why does PostgreSQL not return null values when the condition is <> true

ID : 274338

viewed : 33

Tags : sqlpostgresqlnullcomparisonsql





Top 4 Answer for sql - Why does PostgreSQL not return null values when the condition is <> true

vote vote

96

Every halfway decent RDBMS does it the same way, because it's correct. The manual:

Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs:

expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression 

These expressions perform slightly slower than simple expression <> expression comparison.

For boolean values there is also the simpler IS NOT [TRUE | FALSE].
To get what you expected in your second query, write:

SELECT * FROM tbl WHERE avalue IS NOT TRUE;

db<>fiddle here
Old sqlfiddle

vote vote

82

This link provides a useful insight. Effectively as @Damien_The_Unbeliever points out, it uses Three-valued logic and seems to be (according to the article) the subject of debate.

A couple of other good links can be found here and here.

I think it boils down to null not being a value, but a place holder for a value and a decision had to be made and this was it... so NULL is not equal to any value because it isn't a value and won't even not be equal to any value.... if that makes sense.

vote vote

70

It is normal. SQL Server does it in the same way. In SQL Server you can use

SELECT * FROM table WHERE ISNULL(avalue, 0) <> 1 

For postgresql equivalent watch this: What is the PostgreSQL equivalent for ISNULL()

Consider to use NOT NULL column specification with default value, if it makes sense.

EDIT:

I think it is logic. NULL is not a value, so it is excluded from searching - you have to specify it explicitly. If SQL designers decides to go by second way (include nulls automatically), then you would get more troubles if you need to recognize no values

vote vote

66

Top 3 video Explaining sql - Why does PostgreSQL not return null values when the condition is <> true







Related QUESTION?