SQL nulls are weird • Hacker News Board

Re: SQL nulls are weird

#281

Post by dataflow » Sat, Jan 11, 2025, 4:13 PM UTC

datadrivenangel wrote: post #7

SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

> SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

Could you explain how this makes sense then?

  SELECT ...
  WHERE NULL

If NULL is just "unknown" then shouldn't this be a type error?

Moreover, could you explain why the heck this ought to be empty?

  WITH T AS (SELECT 1 AS C1,
             NULL AS C2)
    SELECT C1, C2
    FROM T
    INNER JOIN T
    USING (C1, C2);

As a human this looks insane to me, "relational algebra" be damned. You find a row, then you look it up again, and it's not there? What the hell?

Re: SQL nulls are weird

#282

Post by DangitBobby » Sat, Jan 11, 2025, 4:27 PM UTC

hot_gril wrote: post #274

DangitBobby wrote: post #268

Earlier quoted context omitted.

I've used ORMs extensively in my career, and I've seen it trotted out as a Boogeyman here a million times. Why do I still prefer writing queries with a good ORM over awkwardly composing SQL by conditionally concatenating strings? Is buggy, error prone string concatenation with a bunch of if statements the true way? What am I missing?

Conditional concat should be pretty rare. When are you doing that?

When I need a new where clause or a new order by, or I have to stop using an IN filter because you can't use an empty tuple. Stuff like that.

Re: SQL nulls are weird

#283

Post by jakubmazanec » Sat, Jan 11, 2025, 5:16 PM UTC

That's why I like EdgeDB and EdgeQL: no null [1]

[1] https://www.edgedb.com/blog/we-can-do-better-than-sql

Re: SQL nulls are weird

#284

Post by iefbr14 » Sat, Jan 11, 2025, 7:57 PM UTC

jfb wrote: post #184

iefbr14 wrote: post #147

When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..

Sometimes you want UNKNOWN, sometimes you want MISSING.

Just give the data item a status field, don't fix it by medling with a designated (non)value. And while you are at it you can add some valid-from and valid-to fields for the item. That's how you do it proper.

Re: SQL nulls are weird

#285

Post by chuckadams » Sun, Jan 12, 2025, 2:12 PM UTC

bregma wrote: post #234

PhilipRoman wrote: post #156

Earlier quoted context omitted.

I haven't had the chance to work on any MMU-less devices, but I don't quite follow your remark about wasting a page. Crashing is just the default behaviour in the absence of a mapping and consumes no resources.

The granularity of virtual address mapping is usually a page. On many systems, that's 4 kilobytes of address space. In order to trigger a fault when the address 0x0000000000000000 is dereferenced, it's necessary to map the entire address range from 0x0000000000000000 to 0x0000000000000fff to the same faulting behaviour. That's a waste of a page.

Any system where losing one page would be too dear is likely not using a MMU in the first place.

Besides, memory access is twice as fast on the zero page! Wait, you're telling me the 6502 isn't the standard anymore? ;p

Re: SQL nulls are weird

#286

Post by hot_gril » Mon, Jan 13, 2025, 6:09 AM UTC

DangitBobby wrote: post #282

hot_gril wrote: post #274

Earlier quoted context omitted.

Conditional concat should be pretty rare. When are you doing that?

When I need a new where clause or a new order by, or I have to stop using an IN filter because you can't use an empty tuple. Stuff like that.

Yeah, if you're going to conditionally add filters, this is mostly the only way with SQL. There are a few tricks depending on the dialect, like =ANY() is more convenient than IN, or COALESCE can avoid some dynamic WHEREs.

You're not really missing anything other than query builders, which are worse for other reasons.

Re: SQL nulls are weird

#287

Post by grahamlee » Mon, Jan 13, 2025, 8:52 AM UTC

setr wrote: post #31

grahamlee wrote: post #11

Earlier quoted context omitted.

Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.

The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake. More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics. Also the x != NULL case is completely curs…

> The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra.

It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.

Re: SQL nulls are weird

#288

Post by dominicrose » Mon, Jan 13, 2025, 9:19 AM UTC

swiftcoder wrote: post #178

hobs wrote: post #166

Earlier quoted context omitted.

No, it's not those other things, that's just using the tool incorrectly. A NULL is definitely "we dont know", not false, not N/A, especially not any known value.

Except in every other programming language with a null, null is the definite absence of something

Yes and I think that Clojure handles nil pretty well, and it's a functional programming language like SQL. It's also interesting to see that Typescript has added an unknown type. So something that's a value in SQL (null being unknown) is a type in TS.

Re: SQL nulls are weird

#289

Post by int_19h » Mon, Jan 13, 2025, 10:52 PM UTC

feoren wrote: post #117

int_19h wrote: post #80

Earlier quoted context omitted.

From a purely relational perspective, if some piece of data can be absent, it's a 1:N relation where N (Of course, this is rather awkward in practice, and when NULL is there, it's inevitably going to be used for that instead.)

It is encoded as such. That's why most columns are made nullable. It's crazy to say you need to use the full power of a 1:N relation with some child table when you know N cannot be greater than 1, when a nullable column already exactly encodes a 1:(0..1) relation. I'm not trying to shill for null here: one of null's great problems is exactly the fact that null can represent "unknown", "known absent", "not applicable"…

I don't know if it's "patently absurd" given that it's a well-known C.J. Date take on NULLs in SQL. You may disagree with him on this - I do - but the very fact that the father of relational algebra has this take should be sufficient evidence that it's not a trivial question to be dismissed without consideration.

Re: SQL nulls are weird

#290

Post by mnsc » Tue, Jan 14, 2025, 8:27 PM UTC

DangitBobby wrote: post #269

mnsc wrote: post #100

Earlier quoted context omitted.

But in a _relational_ database lack of spouse would not be modeled with a nullable column "spouse" but rather an absence of a spouse row/relation. Which is very real-world-like.

And yet when you do a join because you need to actually use that data, the resulting table will have a column with nulls in it. Any way you squeeze it, you need a way to represent empty values for your database to be useful.

An inner join? Then there wouldn't be any nulls.