Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Wed, Mar 31, 2021, at 22:25, Isaac Morland wrote:
>
> Maybe I have a different proposal in mind than anybody else, but I don't think there is a problem with multiple joins to the same table. If the joins are via the same constraint, then a single join is enough, and if they are via different constraints, the constraints have unique names.
>
> I think if TA is a table with a foreign key constraint CB to another table TB, then the hypothetical expression:
>
> TA -> CB
>
> really just means:
>
> (select TB from TB where (TB.[primary key columns) = (TA.[source columns of constraint CB]))
>
> You can then add .fieldname to get the required fieldname. The issue is that writing it this way is hopelessly verbose, but the short form is fine. The query planner also needs to be guaranteed to collapse multiple references through the same constraint to a single actual join (and then take all the multiple fields requested).
>
> If TA is a table with a foreign key constraint CB to TB, which has a foreign key constraint CC to TC, then this expression:
>
> TA -> CB -> CC
>
> just means, by the same definition (except I won't expand it fully, only one level):
>
> (select TC from TC where (TC.[primary key columns) = ((TA -> CB).[source columns of constraint CC]))
>
> Which reminds me, I often find myself wanting to write something like a.(f1, f2, f3) = b.(f1, f2, f3) rather than (a.f1, a.f2, a.f3) = (b.f1, b.f2, b.f3). But that's another story

Maybe “anonymous join” would be a good name for this, similar to anonymous functions. The joined table(s) would not pollute the namespace.

/Joel