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

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

Bruce Momjian <bruce@momjian.us> writes:
> 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.

You should not remove the CROSS JOIN mention at l. 604, first because
the references to it just below would become odd, and second because
then it's not explained anywhere on the page.  Perhaps you could
put back a definition of CROSS JOIN just below the entry for NATURAL,
but you'll still have to do something with the references at l. 614,
628, 632.

Also, doesn't "[ AS join_using_alias ]" apply to NATURAL and CROSS
joins?  You've left that out of the syntax summary.

            regards, tom lane

On Sat, Aug 13, 2022 at 10:21:26PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
> 
> You should not remove the CROSS JOIN mention at l. 604, first because
> the references to it just below would become odd, and second because
> then it's not explained anywhere on the page.  Perhaps you could
> put back a definition of CROSS JOIN just below the entry for NATURAL,
> but you'll still have to do something with the references at l. 614,
> 628, 632.

Good point.  I restrutured the docs to move CROSS JOIN to a separate
section like NATURAL and adjusted the text, patch attached.

> Also, doesn't "[ AS join_using_alias ]" apply to NATURAL and CROSS
> joins?  You've left that out of the syntax summary.

Uh, I only see it for USING in gram.y:

    /* JOIN qualification clauses
     * Possibilities are:
     *  USING ( column list ) [ AS alias ]
     *                        allows only unqualified column names,
     *                        which must match between tables.
     *  ON expr allows more general qualifications.
     *
     * We return USING as a two-element List (the first item being a sub-List
     * of the common column names, and the second either an Alias item or NULL).
     * An ON-expr will not be a List, so it can be told apart that way.
     */
    
    join_qual: USING '(' name_list ')' opt_alias_clause_for_join_using
                    {
                        $$ = (Node *) list_make2($3, $5);
                    }
                | ON a_expr
                    {
                        $$ = $2;
                    }
            ;

    ...

    /*
     * The alias clause after JOIN ... USING only accepts the AS ColId spelling,
     * per SQL standard.  (The grammar could parse the other variants, but they
     * don't seem to be useful, and it might lead to parser problems in the
     * future.)
     */
    opt_alias_clause_for_join_using:
                AS ColId
                    {
                        $$ = makeNode(Alias);
                        $$->aliasname = $2;
                        /* the column name list will be inserted later */
                    }
                | /*EMPTY*/                             { $$ = NULL; }
            ;

which is only used in:

    | table_ref join_type JOIN table_ref join_qual
    | table_ref JOIN table_ref join_qual

I have updated my private build:

    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

 On Mon, Aug 15, 2022 at 10:53:18PM -0400, Bruce Momjian wrote:
> On Sat, Aug 13, 2022 at 10:21:26PM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > 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.
> > 
> > You should not remove the CROSS JOIN mention at l. 604, first because
> > the references to it just below would become odd, and second because
> > then it's not explained anywhere on the page.  Perhaps you could
> > put back a definition of CROSS JOIN just below the entry for NATURAL,
> > but you'll still have to do something with the references at l. 614,
> > 628, 632.
> 
> Good point.  I restrutured the docs to move CROSS JOIN to a separate
> section like NATURAL and adjusted the text, patch attached.

Patch applied back to PG 11.  PG 10 was different enough and old enough
that I skipped it.  This is a big improvement.  Thanks.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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