SQL nulls are weird

Post by hun3 » Fri, Jan 10, 2025, 10:36 AM UTC

dominicrose wrote: post #128

thayne wrote: post #85

Earlier quoted context omitted.

If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.

Javascript has both null and undefined and I'm not sure that's a good idea. At least in SQL we only have one of them, but it can mean unknown or it can mean N/A or even false. It's like a joker, what it means depends on how you use it.

Post by tessierashpool9 » Fri, Jan 10, 2025, 10:42 AM UTC

ozim wrote: post #140

tessierashpool9 wrote: post #134

Earlier quoted context omitted.

there is a pattern starting to emerge here on hackernews of highly voted posts by people who present themselves as experts and thought leaders who shamelessly put their lack of understanding at display. it's frightening.

I see it the other way around. People think if someone wrote blog post with technical details and it got upvoted - somehow it has to be an expert.

Post by mrkeen » Fri, Jan 10, 2025, 10:45 AM UTC

wodenokoto wrote: post #126

bunderbunder wrote: post #51

Earlier quoted context omitted.

The distinction is that not all formal logic systems are Boolean. Meaning that it is nonsensical and confusing to use "Boolean" as a generic synonym for "truth value" in the same way that it's nonsensical to use "Pantone" as a generic synonym for "color value", including when the specific kind of color value you're talking about is CMYK or HSV and definitely not Pantone.

There are two values, TRUE and FALSE. Null is not a value, it the the lack of a value. You have a list of people and you ask if they own a car. You didn't get around to asking George, so that, somehow means he owns a car because you are using boolean logic? Or does it mean he doesn't own a car, because you are using boolean logic? No, it means you haven't gathered this data point and don't know.

  CREATE TABLE people(name text, carId uuid);
  INSERT INTO people values('Bill', '40c8a2d7-1eb9-40a9-b064-da358d6cee2b');
  INSERT INTO people values('Fred', '3446364a-e4a5-400f-bb67-cbcac5dc2254');
  INSERT INTO people values('George', NULL);

  SELECT Count(*) FROM people WHERE name NOT IN (
    SELECT name FROM people WHERE carId = NULL
  );

Post by noduerme » Fri, Jan 10, 2025, 11:03 AM UTC

tessierashpool9 wrote: post #134

hiAndrewQuinn wrote: post #27

SQL NULLs aren't weird , they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNK…

there is a pattern starting to emerge here on hackernews of highly voted posts by people who present themselves as experts and thought leaders who shamelessly put their lack of understanding at display. it's frightening.

Post by kopirgan » Fri, Jan 10, 2025, 11:22 AM UTC