3 ms·

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/14766295506

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 https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.

cpill 5y ago
I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.
- salawat 5y ago
  ...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.
- gavinray 5y ago
  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
rcoveson 5y ago
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.

cpill 5y ago

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

salawat 5y ago
...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.
gavinray 5y ago
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

rcoveson 5y ago

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.