Re: SELECT documentation - Mailing list pgsql-hackers

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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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