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

On Mon, Mar 29, 2021, at 16:17, Vik Fearing wrote:
> SELECT DISTINCT order_details."order"->customer->company_name
> FROM order_details
> WHERE order_details.product->product_name = 'Chocolade';

I like the idea of using -> instead of . (dot),
since name resolution is already complicated,
so overloading the dot operator feels like a bad idea.

I therefore propose the following syntax:

{ table_name | alias } -> constraint_name [ [ -> constraint_name ... ] -> column_name ]

It's necessary to start with the table name or its alias,
since two tables/aliases used in the same query
might have different constraints with the same name.

If the expression ends with a column_name,
you get the value for the column.

If the expression ends with a constraint_name,
you get the referenced table as a record.

I also have a new idea on how we can use
the nullability of the foreign key's column(s),
as a rule to determine if you would get
a LEFT JOIN or an INNER JOIN:

If ALL of the foreign key column(s) are declared as NOT NULL,
then you would get an INNER JOIN.

If ANY of the foreign key column(s) are declared as NULL,
then you would get a LEFT JOIN.

Thoughts?

/Joel