> SQL null handling rules are terrible. I understand them, I work with them, but...

Sankozi · on Jan 25, 2022

In SQL NULL does not mean "no value" it means "unknown value". Existence of such value introduces three value logic because expression "NULL = <anything>" is neither true nor false. This makes queries harder to understand without any benefit.

Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".

dotancohen · on Jan 25, 2022

  > Existence of such value introduces three value logic because expression "NULL = <anything>" is neither true nor false.

Could you elaborate on that? I'm thought that in SQL `NULL=NULL` returns FALSE, much like the floats `NAN==NAN` returns false:

  > select if(null=null, "Yes", "No")
  > +----------------------------+
  > | No                         |
  > +----------------------------+

What does it mean that this is neither TRUE or FALSE? I very much appreciate the lesson!

yencabulator · on Jan 25, 2022

The value in your if is not false, it's NULL. NULL=NULL behaves exactly like NULL=42, the value is NULL. Which is what the parent was trying to explain.

With Postgres:

  postgres=# \pset null <null>
  postgres=# select null, null=null, null=42;
   ?column? | ?column? | ?column?
  ----------+----------+----------
   <null>   | <null>   | <null>
  (1 row)

dotancohen · on Jan 26, 2022

  > mysql> select 42=42,42=314,42=null,null=null;
  > +-------+--------+---------+-----------+
  > | 42=42 | 42=314 | 42=null | null=null |
  > +-------+--------+---------+-----------+
  > |     1 |      0 |    NULL |      NULL |
  > +-------+--------+---------+-----------+

I see, thank you. This behaviour is news to me.

dspillett · on Jan 25, 2022

Not the person you replied to, but I don't think by “from other languages” he means other dialects of SQL.

Instead, I think other languages away from the database are being referred to - in many of those NULL is treated like any other value², for instance in Javascript¹ null==null is true and null!=null is false, and due to type coercion null on its own is “falsey”. Personally I have no problem with SQLs handling of NULL with one exception, and find other languages treating it as a single value rather than an unknown feels odd.

The one thing that I have occasionally tripped over with NULL in SQL is the effect of “<val-or-var> NOT IN (<set>)” when NULL is one of the entries in <set> - it makes sense when you think about it because the IN operator can only return true or false and it can't definitively say the searched for value isn't equal to the unknown one(s)³ but this doesn't seem intuitive.

Some SQL dialects do handle NULL a little differently, more like languages like JS. MS SQL Server can be made to with SET ANSI_NULLS OFF to force its ancient not-standards-compliant behaviour⁴.

[1] quick & easy to test in your browser's console

[2] well, technically in JS I think null is specifically a null object reference, that being one of the differences between null and undefined

[3] more concretely, “var NOT IN (1, 2, NULL)” being equivalent to “var<>1 AND var<>2 AND var<>NULL” which becomes “true AND true AND NULL” which is NULL as any logical operator against NULL returns NULL.

[4] though note that this option is officially deprecated, as of at least 2016, and might be removed or just ignored in future versions

dotancohen · on Jan 25, 2022

  > The one thing that I have occasionally tripped over...this doesn't seem intuitive.

This paragraph led me to discovering this oddity:

  > select 'true' from dual where 3 not in (1, 2, null)
  > Empty set (0.00 sec)

That's unexpected, and definitely something that I should be aware of. Thank you.

yencabulator · on Jan 25, 2022

SQL NULL is like NaN, it never equals anything. (And on top of that, the result of any comparison with NULL isn't a boolean, it's NULL.)

Similarly (except the result is a boolean),

  $ deno eval --print 'NaN == NaN'
  false

dotancohen · on Jan 26, 2022

Thanks.