4 ms·

NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very po

NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very powerful when you understand that.

ryanbrunner 4y ago
That seems like a fairly big assumption on how I'm modelling the data though. Particularly in the LEFT JOIN example, I'm not sure if I agree with the idea of "a row not being present means that it's unknown whether it exists in the real world" depending on the underlying content being modelled. For a string column, you do have blanks vs. NULLs, so it's fairly easy to distinguish between "John definitely doesn't have a middle name" vs "It's unknown whether John has a middle name", but that doesn't hold true with most other datatypes.
- SnowHill9902 4y ago
  The string ‘’ is as arbitrarily blank as 0 is a blank integer.
  - wongarsu 4y ago
    There are two "blank" integers: 0 is the identity element under addition, 1 is the identity element under multiplication. Or worded differently 0 is a blank addend, 1 is a blank multiplier. ‘’ would be the identity element for string concatenation, or any other string manipulation method I can think of, so it has a stronger claim on being blank than a 0 has. Unless your string is later parsed as something else. A blank JSON value would look differently from a blank phone number.
    - SnowHill9902 4y ago
      Sure, but it’s arbitrary from a data structure standpoint and sloppy design.
      - ryanbrunner 4y ago
        SQL gives you no other options though, there's no other construct that means "not present", since NULL is unknown vs. not present.
        SnowHill9902 4y ago
        It depends on your column. If you have a products table and a certain handmade product has no upc, it’s correct to use ‘’ as its value rather than NULL because you know for a fact that its non-existent. Some people respect NULL too much.
woevdbz 4y ago
It is mostly true but not always, eg in the output of GROUP BY ROLLUP, a NULL value is used to indicate rows representing subtotals / partial aggregates

NULL means that the value is unknown, not that the data is unknown. You indeed have no data but its value may exist. You just don’t know it yet. NULL is very powerful when you understand that.

ryanbrunner 4y ago

That seems like a fairly big assumption on how I'm modelling the data though. Particularly in the LEFT JOIN example, I'm not sure if I agree with the idea of "a row not being present means that it's unknown whether it exists in the real world" depending on the underlying content being modelled. For a string column, you do have blanks vs. NULLs, so it's fairly easy to distinguish between "John definitely doesn't have a middle name" vs "It's unknown whether John has a middle name", but that doesn't hold true with most other datatypes.

SnowHill9902 4y ago
The string ‘’ is as arbitrarily blank as 0 is a blank integer.
- wongarsu 4y ago
  There are two "blank" integers: 0 is the identity element under addition, 1 is the identity element under multiplication. Or worded differently 0 is a blank addend, 1 is a blank multiplier. ‘’ would be the identity element for string concatenation, or any other string manipulation method I can think of, so it has a stronger claim on being blank than a 0 has. Unless your string is later parsed as something else. A blank JSON value would look differently from a blank phone number.
  - SnowHill9902 4y ago
    Sure, but it’s arbitrary from a data structure standpoint and sloppy design.
    - ryanbrunner 4y ago
      SQL gives you no other options though, there's no other construct that means "not present", since NULL is unknown vs. not present.
      - SnowHill9902 4y ago
        It depends on your column. If you have a products table and a certain handmade product has no upc, it’s correct to use ‘’ as its value rather than NULL because you know for a fact that its non-existent. Some people respect NULL too much.

woevdbz 4y ago

It is mostly true but not always, eg in the output of GROUP BY ROLLUP, a NULL value is used to indicate rows representing subtotals / partial aggregates