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

On Tue, 30 Mar 2021 at 15:33, Joel Jacobson <joel@compiler.org> wrote:

> Also, should the join be a left join, which would therefore return a NULL
> when there is no matching record? Or could we have a variation such as ->?
> to give a left join (NULL when no matching record) with -> using an inner
> join (record is not included in result when no matching record).
>
>
> Interesting idea, but I think we can keep it simple, and still support the
> case you mention:
>
> If we only have -> and you want to exclude records where the column is
> NULL (i.e. INNER JOIN),
> I think we should just use the WHERE clause and filter on such condition.
>

Just to be clear, it will always be a left join? Agreed that getting the
inner join behaviour can be done in the WHERE clause. I think this is a
case where simple is good. As long as the left join case is supported I'm
happy.


> Thanks for the encouraging words. I have exactly the same experience
> myself and share your view.
>
> I look forward to continued discussion on this matter.
>

I had another idea: maybe the default name of a foreign key constraint to a
primary key should simply be the name of the target table? That is, if I
say:

FOREIGN KEY (...) REFERENCES t

... then unless the table name t is already in use as a constraint name, it
will be used as the constraint name. It would be nice not to have to keep
repeating, like this:

CONSTRAINT t FOREIGN KEY (...) REFERENCES t