3 ms·

>I might be the only person who likes SQL nulls From my understanding, null values are bad because its a sign that the database design is flawed (See database n

>I might be the only person who likes SQL nulls From my understanding, null values are bad because its a sign that the database design is flawed (See database normalizations) Perhaps you have a more practical experience?

RHSeeger 7y ago
The fact that a database is not fully normalized is not a sign that it's flawed. In fact, there are cases where some tables being fully denormalized makes sense (although that's less common in my experience).
giornogiovanna 7y ago
1. NOT NULL is very frequently used in schemas, because NULL is often an undesirable value (e.g. for a mandatory field). That doesn't make NULLs bad, and NULLs are still frequently used when you have optional fields or fields where NULL has some other special meaning. 2. NULLs are used by SQL functions and operators as an "unknown" value. So, for example, "NULL AND TRUE" is NULL, because we could substitute NULL with TRUE or with FALSE to get different results, but "NULL AND FALSE" is FALSE, because no matter what we substitute NULL with, the result will always be FALSE. 3. Clearly all these valid uses of NULL do not indicate "flaws" in the database design. 4. Database normalization isn't always a good thing, and beyond a certain level it's almost always a bad thing, so using normalization methods as a standard for whether something is "flawed" is probablyn ot the best idea. 5. No database normalization method, as far as I know, actually tries to eliminate NULLs, so I don't know what "(See database normalizations)" refers to. Can you clarify?
- tabtab 7y ago
  Re: NOT NULL is very frequently used in schemas, because NULL is often an undesirable value Talking strings, usually if you don't want a null string, you also don't want blanks (white space) either. I'd like to see auto-trim in the standard, and also a minimum length specifier. We then wouldn't need to deal with nulls. A single min-length-when-trimmed-and-denulled integer value would replace a lot of repetitious hubbub in typical CRUD apps. D.R.Y. it! You'd have one attribute "cell" that would replace the equivalent of: IF length(trim(denull(inputValue,''))) < this_fields_min_length THEN raise_data_too_short_error(...); That's the way you'd want it done in the vast vast majority of CRUD systems (if not doing weird things).
kstrauser 7y ago
That's not right. "Null" means that the value is not known. Suppose you have a table for employees, and you want to record the last time they were paid. What do you put in that column for people who just started this morning? The alternatives are to use null, indicating that they haven't been, or to formulate a codebase-wide sentinel value like "0000-01-01" and then accounting for that in every single database operation everywhere. Further suppose that you have an external function in your codebase to estimate how many paychecks you've paid to someone, but the author doesn't know about any "0000-01-01" conventions your office uses. Without that, you'd see that Joe New Guy has worked here about 2,020 years, so we've probably issued him about 48,000 checks. If only you'd used null, then that function would have calculated "today() - null", which in any sane language would raise a type exception and alert you to the problem. Nulls are beautiful. They have meaning. Lots of people misuse them, but that doesn't mean they're not valid and useful.
- oarabbus_ 7y ago
  this is 100% how I feel. It's mindboggling that some people here think NULL is "wrong" or should be avoided. Nulls are, as you said, beautiful.
  - bena 7y ago
    A quick question I use to demonstrate the usefulness of NULL is "What color is the elephant on my desk?" That question doesn't have an answer because there is no elephant on my desk. It can't be represented by any color, the answer needs to indicate that there is no value.
    - rileymat2 7y ago
      The result may be null when asking the question, but the tables representing this reality do not need a null column. (Specifically, not returning any rows when queried, which is different from null)
      - GauntletWizard 7y ago

RHSeeger 7y ago

The fact that a database is not fully normalized is not a sign that it's flawed. In fact, there are cases where some tables being fully denormalized makes sense (although that's less common in my experience).

giornogiovanna 7y ago

1. NOT NULL is very frequently used in schemas, because NULL is often an undesirable value (e.g. for a mandatory field). That doesn't make NULLs bad, and NULLs are still frequently used when you have optional fields or fields where NULL has some other special meaning. 2. NULLs are used by SQL functions and operators as an "unknown" value. So, for example, "NULL AND TRUE" is NULL, because we could substitute NULL with TRUE or with FALSE to get different results, but "NULL AND FALSE" is FALSE, because no matter what we substitute NULL with, the result will always be FALSE. 3. Clearly all these valid uses of NULL do not indicate "flaws" in the database design. 4. Database normalization isn't always a good thing, and beyond a certain level it's almost always a bad thing, so using normalization methods as a standard for whether something is "flawed" is probablyn ot the best idea. 5. No database normalization method, as far as I know, actually tries to eliminate NULLs, so I don't know what "(See database normalizations)" refers to. Can you clarify?

tabtab 7y ago
Re: NOT NULL is very frequently used in schemas, because NULL is often an undesirable value Talking strings, usually if you don't want a null string, you also don't want blanks (white space) either. I'd like to see auto-trim in the standard, and also a minimum length specifier. We then wouldn't need to deal with nulls. A single min-length-when-trimmed-and-denulled integer value would replace a lot of repetitious hubbub in typical CRUD apps. D.R.Y. it! You'd have one attribute "cell" that would replace the equivalent of: IF length(trim(denull(inputValue,''))) < this_fields_min_length THEN raise_data_too_short_error(...); That's the way you'd want it done in the vast vast majority of CRUD systems (if not doing weird things).

kstrauser 7y ago

That's not right. "Null" means that the value is not known. Suppose you have a table for employees, and you want to record the last time they were paid. What do you put in that column for people who just started this morning? The alternatives are to use null, indicating that they haven't been, or to formulate a codebase-wide sentinel value like "0000-01-01" and then accounting for that in every single database operation everywhere. Further suppose that you have an external function in your codebase to estimate how many paychecks you've paid to someone, but the author doesn't know about any "0000-01-01" conventions your office uses. Without that, you'd see that Joe New Guy has worked here about 2,020 years, so we've probably issued him about 48,000 checks. If only you'd used null, then that function would have calculated "today() - null", which in any sane language would raise a type exception and alert you to the problem. Nulls are beautiful. They have meaning. Lots of people misuse them, but that doesn't mean they're not valid and useful.

oarabbus_ 7y ago
this is 100% how I feel. It's mindboggling that some people here think NULL is "wrong" or should be avoided. Nulls are, as you said, beautiful.
- bena 7y ago
  A quick question I use to demonstrate the usefulness of NULL is "What color is the elephant on my desk?" That question doesn't have an answer because there is no elephant on my desk. It can't be represented by any color, the answer needs to indicate that there is no value.
  - rileymat2 7y ago
    The result may be null when asking the question, but the tables representing this reality do not need a null column. (Specifically, not returning any rows when queried, which is different from null)
    - GauntletWizard 7y ago