Select Reference Page - Make Join Syntax More Prominent
Hey,
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.
In this specific case I also was trying to describe why when you have three
tables to join that you can build a join tree and only actually use a
single from_item (IOW, no commas are required/allowed). From there it
became clear that from_item is serving two roles here and introducing a
structural element (join_expression) to represent the commonly used join
tree query form made sense. I then modelled the tree-like nature (using
the term recursive for now) explicitly as the left side of the join can be
either a new join_expression, extending the tree, or a from_item, bringing
the tree to its end.
In the description I had to move LATERAL up above all the join stuff since
it pertains to from_item and I wanted to call out the final set of
parameters as all being related to the join tree.
I'll do a pass at making sure we are using consistent terminology if this
has a shot of getting committed.
I've attached the patch and the resulting html page.
David J.
"David G. Johnston" <david.g.johnston@gmail.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.
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.
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,
even if that means we need a forward reference or two. I'd almost
put it into its own sub-section of "FROM Clause".
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.
regards, tom lane
On Thu, Jul 7, 2022 at 2:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.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.