5 ms·

Why is null weird? Null means unknown. It's perfectly valid.

masklinn 4y ago
“Valid” and “weird” are orthogonal concepts. That sql null is valid doesn’t make its behaviour of not being equal to itself any less weird, because it works that way essentially nowhere else, and thus is highly unintuitive to developers who don’t live and breathe sql. The closest thing in most langages is nan and developers also find nan weird.
- nicoburns 4y ago
  > The closest thing in most langages is nan and developers also find nan weird. I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have. YMMV, but personally the unique index behaviour was the main thing I found weird about NULLs. So now that I can turn that off I'm pretty happy with them.
  - masklinn 4y ago
    > I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have. In what sense? null-coalescing operators don’t change how nulls behave or relate to one another, they only provide convenience operators for conditioning operation upon them, like sql’s COALESCE.
- JUNGLEISMASSIVE 4y ago
- gigatexal 4y ago
  Think of nulls like different infinities. Two infinities may not be equal. I find it quite useful to know when something is null though I have a hard time explaining it.
roenxi 4y ago
SQL uses [0] 3 valued logic, and most people seem to reason using 2-valued logic. So most people fund the behaviour of NULL, the 3rd logical value, to be weird. > It's perfectly valid. :o You'll get jumped on, saying this to a forum of software people. Lots of things that are valid are also weird. [0] https://www.postgresql.org/docs/14/functions-logical.html https://www.postgresql.org/docs/14/functions-logical.html
vasachi 4y ago
It is kinda weird, if you don't know how it works. For instance: Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique: (1, 2) (1, 3) (2, 2) But these values ARE ALSO UNIQUE: (1, NULL) (1, NULL) It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise. ... Oh wait, postgres 15 deals with just this situation. Huh.
- jhugo 4y ago
  Honestly it's what you want way more often than not. A unique index on a nullable column is usually representing some optional identifier. You wouldn't usually want only one row to be able to omit the identifier.
  - vasachi 4y ago
    Oh yeah, totally. But not always. I'm very glad that we now have an option to have both behaviours.
Sankozi 4y ago
It makes lots of queries (like find duplicate queries) much more complex without any benefit. Also it introduces completely unnecessary three value logic in SQL.
- gigatexal 4y ago
  That’s a bold claim. There are constructs to handle nulls for such queries.
ryanbrunner 4y ago
With keys it very often means "definitively not present" in practice, and there's no other value that you can use to represent that if you want referential integrity.

“Valid” and “weird” are orthogonal concepts. That sql null is valid doesn’t make its behaviour of not being equal to itself any less weird, because it works that way essentially nowhere else, and thus is highly unintuitive to developers who don’t live and breathe sql. The closest thing in most langages is nan and developers also find nan weird.

nicoburns 4y ago
> The closest thing in most langages is nan and developers also find nan weird. I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have. YMMV, but personally the unique index behaviour was the main thing I found weird about NULLs. So now that I can turn that off I'm pretty happy with them.
- masklinn 4y ago
  > I'd argue the closest thing in most languages is the null propagation operator (usually using a ? symbol) that at least JavaScript and C# have. In what sense? null-coalescing operators don’t change how nulls behave or relate to one another, they only provide convenience operators for conditioning operation upon them, like sql’s COALESCE.
JUNGLEISMASSIVE 4y ago
gigatexal 4y ago
Think of nulls like different infinities. Two infinities may not be equal. I find it quite useful to know when something is null though I have a hard time explaining it.

roenxi 4y ago

SQL uses [0] 3 valued logic, and most people seem to reason using 2-valued logic. So most people fund the behaviour of NULL, the 3rd logical value, to be weird. > It's perfectly valid. :o You'll get jumped on, saying this to a forum of software people. Lots of things that are valid are also weird. [0] https://www.postgresql.org/docs/14/functions-logical.html https://www.postgresql.org/docs/14/functions-logical.html

vasachi 4y ago

It is kinda weird, if you don't know how it works. For instance: Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique: (1, 2) (1, 3) (2, 2) But these values ARE ALSO UNIQUE: (1, NULL) (1, NULL) It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise. ... Oh wait, postgres 15 deals with just this situation. Huh.

jhugo 4y ago
Honestly it's what you want way more often than not. A unique index on a nullable column is usually representing some optional identifier. You wouldn't usually want only one row to be able to omit the identifier.
- vasachi 4y ago
  Oh yeah, totally. But not always. I'm very glad that we now have an option to have both behaviours.

Sankozi 4y ago

It makes lots of queries (like find duplicate queries) much more complex without any benefit. Also it introduces completely unnecessary three value logic in SQL.

gigatexal 4y ago
That’s a bold claim. There are constructs to handle nulls for such queries.

ryanbrunner 4y ago

With keys it very often means "definitively not present" in practice, and there's no other value that you can use to represent that if you want referential integrity.