5 ms·

Null in not-SQL (which is most things) usually isn't this tortured and isn't hard.

rad_gruchalski 2y ago
Because they're two orthogonal problems. It's not like you do select distinct from program variables group by scope in your frontend programming language.
mathw 2y ago
That's because null in not-SQL is a rather different concept, and while it's pretty easy to understand it's absolutely is hard to actually work with. Hoare didn't call it a "billion dollar mistake" on a whim.
swiftcoder 2y ago
I don't know about that. null/undefined in javascript gives it a pretty good run for it's money
gigatexal 2y ago
Yeah the 3 valued logic of SQL trips people up, me too from time to time
- deleted 2y ago
  [deleted]
- mulmen 2y ago
  SQL is not three valued. Neither is NULL. BOOLEAN is accused of being three-valued but it has two values and like all values they can be unknown. Similarly a SMALLINT has 65,536 possible values not 65,537.
  - gigatexal 2y ago
    It’s not? https://modern-sql.com/concept/three-valued-logic https://modern-sql.com/concept/three-valued-logic
    - mulmen 2y ago
      It’s not. Your link makes the same mistake I already addressed. It conflates nullable booleans with tri-state logic. Null is not a value. It is the absence of a value. > The SQL null value basically means “could be anything”. This is wrong. Null means it could be any valid value but that value is unknown. If the datatype is DATE then the value cannot be boolean TRUE or the string ‘purple’.
      - gigatexal 2y ago
        How is that different than “anything”? If I’m comparing a value of type date to a null I still think it works as it should if value is “unknown”. What greater insight or context do we have if it’s a small-int null?
        mulmen 2y ago
        > How is that different than “anything”? Because the possible values are known. > What greater insight or context do we have if it’s a small-int null? The insight is that null is not a value. It’s not a smallint or a boolean or a date. It’s the absence of a possible value. The only way to see a null as tri-state is to conflate it with a nullable boolean. This is an incorrect mental model which leads to confusion.
        rawgabbit 2y ago
        Suppose you have table with two columns to represent a classroom’s exam scores. It has the columns student_id (varchar) and score (int). If a student is sick and has not taken the exam, yes you could enter -99 to represent they did not take the test. But if you want to find the class average, you would have to do something like this: select average(case when score =-99 then null else score end) as class_avg from … Or you could have entered null to begin with.

Because they're two orthogonal problems. It's not like you do select distinct from program variables group by scope in your frontend programming language.

mathw 2y ago

That's because null in not-SQL is a rather different concept, and while it's pretty easy to understand it's absolutely is hard to actually work with. Hoare didn't call it a "billion dollar mistake" on a whim.

swiftcoder 2y ago

I don't know about that. null/undefined in javascript gives it a pretty good run for it's money

gigatexal 2y ago

Yeah the 3 valued logic of SQL trips people up, me too from time to time

deleted 2y ago
[deleted]
mulmen 2y ago
SQL is not three valued. Neither is NULL. BOOLEAN is accused of being three-valued but it has two values and like all values they can be unknown. Similarly a SMALLINT has 65,536 possible values not 65,537.
- gigatexal 2y ago
  It’s not? https://modern-sql.com/concept/three-valued-logic https://modern-sql.com/concept/three-valued-logic
  - mulmen 2y ago
    It’s not. Your link makes the same mistake I already addressed. It conflates nullable booleans with tri-state logic. Null is not a value. It is the absence of a value. > The SQL null value basically means “could be anything”. This is wrong. Null means it could be any valid value but that value is unknown. If the datatype is DATE then the value cannot be boolean TRUE or the string ‘purple’.
    - gigatexal 2y ago
      How is that different than “anything”? If I’m comparing a value of type date to a null I still think it works as it should if value is “unknown”. What greater insight or context do we have if it’s a small-int null?
      - mulmen 2y ago
        > How is that different than “anything”? Because the possible values are known. > What greater insight or context do we have if it’s a small-int null? The insight is that null is not a value. It’s not a smallint or a boolean or a date. It’s the absence of a possible value. The only way to see a null as tri-state is to conflate it with a nullable boolean. This is an incorrect mental model which leads to confusion.
      - rawgabbit 2y ago
        Suppose you have table with two columns to represent a classroom’s exam scores. It has the columns student_id (varchar) and score (int). If a student is sick and has not taken the exam, yes you could enter -99 to represent they did not take the test. But if you want to find the class average, you would have to do something like this: select average(case when score =-99 then null else score end) as class_avg from … Or you could have entered null to begin with.