1

We have a case in Postgres where

where p.is_ready_for_submission != 'N'

is failing to include NULL as a satisfying value for this condition. The column is_ready_for_submission can be NULL, Y, N. The purpose of the statement above is to include NULL and Y. But the NULLs are not included (they were in Oracle).

The only way to achieve this was to re-write as

(p.is_ready_for_submission is null or p.is_ready_for_submission = 'Y')

Is this special to Postgres? Note: this happens with both != and <>.

gene b.
  • 10,512
  • 21
  • 115
  • 227
  • I would be surprised if this was different in Oracle. Maybe try the standard `<>` instead of `!=`? – Bergi May 15 '22 at 22:02
  • Correct, if the where condition is null in postgresql that counts as not satisfied. I'd be surprised if this was unique to postgresql (wouldn't the opposite be more surprising to a beginner?), but I haven't done a survey. – Dan Getz May 15 '22 at 22:05
  • I tried both `!=` and `<>`. – gene b. May 15 '22 at 22:06
  • I think sqlite *did* do a survey of null handling to help choose how they handled nulls in various situations, and included it in their documentation; you might want to look there. – Dan Getz May 15 '22 at 22:08
  • "*But the NULLs are not included (they were in Oracle)*" - most certainly **not**. https://dbfiddle.uk/?rdbms=oracle_18&fiddle=89cebaf88f34b825401afd974511e24c –  May 16 '22 at 05:38

2 Answers2

7

No, this isn't specific to Postgres, it's the same in Oracle (online example a, online example b). A != or <> expression where one of the operands is NULL will evaluate to NULL, and this will be considered falsy in the WHERE clause.

To include NULL values in your results, you can use the SQL-1999 standard

p.is_ready_for_submission IS DISTINCT FROM 'n'

which will not propagate NULL values but consider them as distinct values.

Unfortunately, it's not supported in Oracle, various alternatives exist (like the one in your question).

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
2

It's universal and a fundamental premise of SQL: Any evaluation of a null results in null, which is not true.

But there's a universal workaround for a simplification:

where coalesce(p.is_ready_for_submission, 'N') != 'Y')

or possibly more clearly expressed:

where coalesce(p.is_ready_for_submission, 'Y') == 'Y')

The coalesce() function, which is part of the SQL standard, returns the first non-null parameter.

Certain databases have specific support:

MySQL

MySQL has the "null safe" equality operator <=>:

where not p.is_ready_for_submission <=> 'N'

See live demo.

Postgres

Postgres has the "null safe" is distinct from syntax:

where p.is_ready_for_submission is distinct from 'N'

See live demo.

Bohemian
  • 412,405
  • 93
  • 575
  • 722