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.
Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".