CODEWITHSUNDEEP
postgresql
Evgeny
Evgeny

Reputation: 2161

Comparing with a value which can be NULL

The following expression

SELECT * FROM t
WHERE a = (SELECT MAX(a) FROM s)

returns nothing when the subquery returns NULL - even if there are NULLs in t.a.

I would like to return NULL values from t when the subquery returns NULL and non-NULL values from t if there are any matching the subquery.

Is there a clean way to do it?

Upvotes: 3

Views: 82

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21376

The expression x IS NOT DISTINCT FROM y is equivalent to (x = y) OR (x IS NULL AND y IS NULL). So:

SELECT * FROM t
WHERE a IS NOT DISTINCT FROM (SELECT MAX(a) FROM s)

Upvotes: 4

Related Questions