For Want of a JOIN

Re:For Want of a JOIN by lmm on Thursday December 22, 2022 @10:04PM (#34101919)

The things that are bad about SQL have nothing to do with set theory. The non-orthogonal syntax. The terribly limited and opaque type system. Ternary logic that fails silently. The utter lack of composability or testability.

[ Parent | on HN ]

Re:For Want of a JOIN by ttfkam on Thursday December 22, 2022 @11:06PM (#34102240)

CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there.

Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key.

3 = NULL

"Is 3 equal to this unknown value?" Maybe yes. Maybe no. It's unknown. Therefore the answer to "3 = NULL" is NULL. The answer is also unknown. Not true. Not false. Unknown.

IS NULL or IS NOT NULL, but never = NULL or <> NULL.

It may be unusual to someone coming from a general purpose programming language's notion of null as a (known) missing value, but that doesn't make it wrong. It means you need to reorient your mind toward set theory, where NULL means "unknown" if you're going to work with SQL and relational databases in general.

Folks often speak of the impedance mismatch between relational models and in-memory object models. NULL is one of those mismatches.

[ Parent | on HN ]

Re:For Want of a JOIN by lmm on Thursday December 22, 2022 @11:13PM (#34102270)

> CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there.

> Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an unknown value make sense in that context? Many times not, and the column should either not be nullable or should be referenced in a different table by a foreign key.

Cool, now how do I do these two incredibly basic things at the same time and make something not nullable or referencing another table in a CTE?

[ Parent | on HN ]

Re:For Want of a JOIN by fomine3 on Thursday December 22, 2022 @11:51PM (#34102447)

CTEs are quite composable. -- on SQL land.

I wish SQL is more composable

[ Parent | on HN ]

Re:For Want of a JOIN by ttfkam on Friday December 23, 2022 @01:09PM (#34108848)

Also, views are the essence of composability.

As for testing in the Postgres sphere, there's pgTAP and pg_prove. https://pgtap.org/

Extension development includes a built-in unit testing harness as well.

For use with any database, a tool like Sqitch allows for DB-agnostic verification at each migration point. https://sqitch.org/docs/manual/sqitchtutorial/#trust-but-ver...

Proprietary databases have their own solutions as well. https://learn.microsoft.com/en-us/sql/ssdt/walkthrough-creat...

I think "utter lack" is grossly misrepresenting the state of the art. If you mean "widespread ignorance of existing techniques related to composability or testability," then we are in agreement.

[ Parent | on HN ]