News for hackers. Stuff that matters.
Re:PRQL – A proposal for a better SQL by Sankozi on Tuesday January 25, 2022 @05:46AM (#30070392)
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".

Re:PRQL – A proposal for a better SQL by dotancohen on Tuesday January 25, 2022 @07:41AM (#30071121)

  > 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!
Re:PRQL – A proposal for a better SQL by yencabulator on Tuesday January 25, 2022 @11:14AM (#30073549)
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)
Re:PRQL – A proposal for a better SQL by dotancohen on Wednesday January 26, 2022 @02:52AM (#30082933)

  > 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.