Hacker News Reader

Comment by benjiweber | Hacker News Reader

NATURAL JOIN is great because it's like a relational AND. How else will you join with Table Dee and Table Dum? https://twitter.com/benjiweber/status/1476629550608101384

It's just risky if you don't design your schema with relational algebra in mind.

View on HN

That is a really interesting case. Correct me if I'm wrong, but I don't think this is a special case where you have to use `NATURAL JOIN` over `JOIN ON`. The problem is just that the Postgres SQL grammar (maybe most/all SQL grammars) require ON or USING in a non-NATURAL join, so you have to specify the (empty) equijoin predicate list yourself, i.e. the identity value: `JOIN ON TRUE`.

EDIT: I had another thought about this.

I think people not designing with the relational algebra in mind is the heart of the issue, specifically w.r.t. column names. We know that namespaces are a hard problem, and a consequence of that problem is that `NATURAL JOIN` as specified in the relational algebra seems risky, or overly magick-y. It makes what might be an unfortunate coincidence (name collision) into something algebraically impactful.

A foreign key join gets around the problem by keeping names and namespaces out of it. It's really doing exactly what `NATURAL JOIN` is supposed to do, but only in the subset of cases where name collisions are meaningful, not coincidental.

I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.

...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.

SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences.

SQL is a language that implements Relational Algebra/Relational Calculus

Reply on news.ycombinator.com