Comment by dotancohen

4 years ago

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

2 comments

dotancohen

yencabulator 4 years ago

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 4 years ago

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