CODEWITHSUNDEEP
sqlpostgresql
Zizheng Tai
Zizheng Tai

Reputation: 6646

Using NULL in WHERE clause

(Note: I'm not asking how to compare a column with NULL.)

I notice that in PostgreSQL if we do SELECT 42 WHERE NULL, the result set is empty. Therefore, something like SELECT c FROM t WHERE c > 0 will select all rows from t where c is both not NULL and greater than zero (because when c is NULL the condition c > 0 evaluates to NULL).

What I'm not sure is whether I can rely on this behavior, because WHERE NULL seems like a hack. (Probably the more proper way is using a CASE expression.)

Upvotes: 4

Views: 9863

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

This is not Postgres-specific behavior. It is how SQL is defined and how NULL values are defined in SQL.

The condition:

WHERE c > 0

is an affirmative condition. It keeps all rows where c > 0 evaluates to true.

When c is NULL, then c > 0 evaluates to NULL. NULL is not true (neither is it false), so c > 0 filters out NULL values. Almost all comparisons to NULL return NULL.

In addition:

WHERE NOT (c > 0)

also filters out NULL values, because NOT NULL is the same as NULL.

If you want to keep NULL values, I would recommend being explicit:

WHERE c > 0 OR c IS NULL

Upvotes: 6

fvu
fvu

Reputation: 32983

According to my interpretation of the documentation you can rely on the fact that only non-null elements will be returned:

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 predicates:

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than "unknown".

To check whether a value is or is not null, use the predicates:

expression IS NULL
expression IS NOT NULL

or the equivalent, but nonstandard, predicates:

expression ISNULL
expression NOTNULL

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

Upvotes: 2

Related Questions