Re: SELECT documentation

On Thu, Dec 30, 2021 at 12:11:26AM +0100, Joel Jacobson wrote:
> Hi,
>
> The Examples section in the documentation for the SELECT command [1]
> only contains a single example on how to join two tables,
> which is written in SQL-89 style:
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d, films f
> WHERE f.did = d.did
>
> I think it's good to keep this example query as it is,
> and suggest we add the following equivalent queries:
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> JOIN films f ON f.did = d.did
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> JOIN films f USING (did)
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> NATURAL JOIN films f

Hi, I agree we should show the more modern JOIN sytax. However, this is
just an example, so one example should be sufficient. I went with the
first one in the attached patch.

Should we link to the join docs?

https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM

I didn't see anything additional there that would warrant a link.

> I also think it would be an improvement to break up the from_item below into
> three separate items,
> since the optional NATURAL cannot occur in combination with ON nor USING.
>
> from_item [ NATURAL ] join_type from_item [ ON join_condition | USING (
> join_column [, ...] ) [ AS join_using_alias ] ]

Agreed. I am surprised this has stayed like this for so long --- it is
confusing.

> Suggestion:
>
> from_item join_type from_item ON join_condition
> from_item join_type from_item USING ( join_column [, ...] ) [ AS
> join_using_alias ]
> from_item NATURAL join_type from_item
>
> This would be more readable imo.
> I picked the order ON, USING, NATURAL to match the order they are described in
> the FROM Clause section.

I went a different direction, since I was fine with ON/USING being a
choice, rather than optional. Also, CROSS JOIN can't use a join_type,
so I split the one line into three in the attached patch, and verified
this from gram.y. Our join docs have this clearly shown:

https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM

from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }
from_item NATURAL join_type from_item
from_item CROSS JOIN from_item

but for some reason SELECT had them all mashed together. Should I
split ON/USING on separate lines?

You can see the result here:

https://momjian.us/tmp/pgsql/sql-select.html

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson