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. So Innistrad's plot becomes far simpler. Jace Beleren went mad because Emrakul tried to explain SQL's NULL to him. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
