Re: Select Reference Page - Make Join Syntax More Prominent

On Thu, Jul 7, 2022 at 2:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Looking again at the SELECT Reference page while helping a novice user I
> > was once again annoyed but how the most common query syntax form for the
> > FROM clause is buried within a bunch of "how to generate a table" detail.
>
> Hmm ... I'm good with the concept of making JOIN syntax more prominent,
> but I don't much like this patch. I think it's fundamentally wrong to
> describe from_item as disjoint from join_expression, and you're
> going to make people more confused not less so by doing that.
>

I'm not so sure about this - but in any case, as you note below, some of
this probably would be better placed in Chapter 7. My impression is that
this aspect is dense and confusing enough as-is that people are learning
the syntax elsewhere and not worrying about how we express it here. My
desire is to bring some design theory aspects to the docs and have the
syntax expression align with the design. I'll do another pass to try and
hopefully find some middle ground here; or be more convincing.

> IMO there's nothing really wrong with the synopsis. The problem is
> in the "FROM Clause" section, which dives headfirst into the weedy
> details without any context. What do you think of adding an
> introductory para or two in that section, saying that the FROM
> clause is built from base tables possibly joined into join expressions?
> You sort of have that here, but it's pretty terse still. Maybe it
> would be helpful also to separate the subsequent list of syntax
> details into base-table options and join syntax.
>

I really don't think focusing on base tables is the best choice here.
Frankly, those end up being fairly trivial. When you start adding lateral
(especially if introduced by comma instead of a join), and subqueries more
generally, that understanding how the pieces compose becomes more useful.
And by disenjoining the from_item and join_item it becomes easier to give
each a purpose and describe how they relate to each other, rather than
trying to paper over their differences. If anything I think that having
from_item be used within the join_item syntax and directly under FROM maybe
be detrimental. Instead, have a term just for the items comma-separated in
the FROM clause, the ones that are CROSS JOINed and use the WHERE clause
for restrictions (or maybe not...).

>
> Not sure what I think about moving LATERAL up. That's a sufficiently
> weird/obtuse thing that I think we're best off dealing with it last,
>

Strongly disagree given how useful set returning functions can be. Part of
the reason I'm doing this now is a recent spate of questions where the
advice that I gave was to write an SRF in a joined lateral, what I consider
at least to be the idiomatic query structure for that use case.

> There's also an argument that the reference page *should* be terse
> and the place to cater to novices is 7.2's "Table Expressions"
> discussion (which we could, but don't, link from the ref page).
> I'm not sure if there's any good way to rework that material to make
> it clearer, but there are definitely bits of it that I don't find
> very well-written. There might be an argument for jettisoning
> some details (like the obsolete "table*" notation) from 7.2
> altogether and covering those only in the ref page.
>
>
Agreed, I will see about allocating material between the two sections
better on the next pass.

I probably need to understand ROWS FROM syntax better as well to make sure
it fits into the mental model I am trying to create.

David J.