News for hackers. Stuff that matters.
Re:SQL NULLs Are Weird by drpossum on Friday January 10, 2025 @05:22AM (#42654401)
Null in not-SQL (which is most things) usually isn't this tortured and isn't hard.
Re:SQL NULLs Are Weird by rad_gruchalski on Friday January 10, 2025 @07:32AM (#42655072)
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.
Re:SQL NULLs Are Weird by mathw on Friday January 10, 2025 @08:22AM (#42655381)
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.
Re:SQL NULLs Are Weird by swiftcoder on Friday January 10, 2025 @09:39AM (#42655912)
I don't know about that. null/undefined in javascript gives it a pretty good run for it's money
Re:SQL NULLs Are Weird by gigatexal on Friday January 10, 2025 @10:53AM (#42656716)
Yeah the 3 valued logic of SQL trips people up, me too from time to time
Re:SQL NULLs Are Weird by mulmen on Friday January 10, 2025 @11:08AM (#42656876)
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.
Re:SQL NULLs Are Weird by gigatexal on Friday January 10, 2025 @01:29PM (#42658448)
Re:SQL NULLs Are Weird by mulmen on Friday January 10, 2025 @01:34PM (#42658505)
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’.

Re:SQL NULLs Are Weird by gigatexal on Friday January 10, 2025 @03:20PM (#42659679)
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?

Re:SQL NULLs Are Weird by mulmen on Friday January 10, 2025 @03:58PM (#42660127)
> 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.

Re:SQL NULLs Are Weird by rawgabbit on Friday January 10, 2025 @08:21PM (#42662411)
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.