News for hackers. Stuff that matters.
Re:SQL NULLs Are Weird by mrkeen on Friday January 10, 2025 @05:45AM (#42654512)
> No, it means you haven't gathered this data point and don't know.

This is how it should be.

> Somehow means he owns a car because you are using boolean logic?

This is how it unfortunately is. There are 3 people, and there are 3 people who don't have a NULL car. Therefore George has a car.

  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
  );
Elsewhere people have argued that NULL propagates, so that your small unknowns infect larger queries. I could get behind that line of thinking, but the query above confidently returns 3.
Re:SQL NULLs Are Weird by mrighele on Friday January 10, 2025 @07:55AM (#42655201)
> There are 3 people, and there are 3 people who don't have a NULL car.

This is not what you are asking with your query: as someone else stated, NULL is meant to be "UNKNOWN", or "it could be any valid value".

So nothing is ever equal to something that can be anything, because even another NULL (i.e. unknown) value is in general different.

So in the line

    SELECT name FROM people WHERE carId = NULL
the condition will always be false. Now if instead if meant to search for the rows where carId is actually unknown you have to write

    SELECT name FROM people WHERE carId is NULL
And your query will return as one may expect 2.
Re:SQL NULLs Are Weird by mrkeen on Saturday January 11, 2025 @05:03AM (#42664706)
> So nothing is ever equal to something that can be anything

It's worse than that. It's something is neither equal nor not-equal to something else.

Whether you can implement something differently as a workaround is immaterial. It's weird, per the title.