On 2016-08-06, Chris Angelico wrote: > On Sun, Aug 7, 2016 at 5:37 AM, Bernd Nawothnig ><bernd.nawoth...@t-online.de> wrote: >>> But SQL's NULL is a cross between C's NULL, IEEE's NaN, Cthulhu, and >>> Emrakul. >> >> SQL NULL has the semantic of "unknown". So if one or both operands of >> a comparison (or any other operation) are unknown the result is >> unknown too. And that means NULL. > > That's not entirely accurate, and it doesn't explain why NULL > sometimes behaves as if it's a genuine value, and sometimes as if it's > completely not there. For instance, taking the average of a column of > values ignores NULLs completely, and COUNT(column) is the same as > COUNT(column) WHERE column IS NOT NULL; but in some situations it > behaves more like NaN: > > rosuav=> select null or true, null or false, null and true, null and false; > ?column? | ?column? | ?column? | ?column? > ----------+----------+----------+---------- > t | NULL | NULL | f > (1 row) > > Anything "or true" has to be true, so NULL OR TRUE is true. And then > there are the times when NULL acts like a completely special value, > for instance in a foreign key - it means "there isn't anything on the > other end of this relationship", and is perfectly legal. Or in a > SELECT DISTINCT, where NULL behaves just like any other value - if > there are any NULL values in the column, you get back exactly one NULL > in the result.
Thanks for that additions and corrections. Bernd -- no time toulouse -- https://mail.python.org/mailman/listinfo/python-list
