On Sat, Aug 13, 2022 at 10:21:26PM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> 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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
new file mode 100644
index 410c80e..1f9538f
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** SELECT [ ALL | DISTINCT [ ON ( <replacea
*** 59,65 ****
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
[ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
! <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] ]
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
--- 59,67 ----
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
[ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
! <replaceable class="parameter">from_item</replaceable> <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> { ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] }
! <replaceable class="parameter">from_item</replaceable> NATURAL <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable>
! <replaceable class="parameter">from_item</replaceable> CROSS JOIN <replaceable class="parameter">from_item</replaceable>
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
*************** TABLE [ ONLY ] <replaceable class="param
*** 600,618 ****
<listitem>
<para><literal>FULL [ OUTER ] JOIN</literal></para>
</listitem>
- <listitem>
- <para><literal>CROSS JOIN</literal></para>
- </listitem>
</itemizedlist>
For the <literal>INNER</literal> and <literal>OUTER</literal> join types, a
join condition must be specified, namely exactly one of
! <literal>NATURAL</literal>, <literal>ON <replaceable
! class="parameter">join_condition</replaceable></literal>, or
<literal>USING (<replaceable
! class="parameter">join_column</replaceable> [, ...])</literal>.
! See below for the meaning. For <literal>CROSS JOIN</literal>,
! none of these clauses can appear.
</para>
<para>
--- 602,616 ----
<listitem>
<para><literal>FULL [ OUTER ] JOIN</literal></para>
</listitem>
</itemizedlist>
For the <literal>INNER</literal> and <literal>OUTER</literal> join types, a
join condition must be specified, namely exactly one of
! <literal>ON <replaceable
! class="parameter">join_condition</replaceable></literal>,
<literal>USING (<replaceable
! class="parameter">join_column</replaceable> [, ...])</literal>,
! or <literal>NATURAL</literal>. See below for the meaning.
</para>
<para>
*************** TABLE [ ONLY ] <replaceable class="param
*** 623,639 ****
In the absence of parentheses, <literal>JOIN</literal>s nest
left-to-right. In any case <literal>JOIN</literal> binds more
tightly than the commas separating <literal>FROM</literal>-list items.
! </para>
!
! <para><literal>CROSS JOIN</literal> and <literal>INNER JOIN</literal>
! produce a simple Cartesian product, the same result as you get from
! listing the two tables at the top level of <literal>FROM</literal>,
! but restricted by the join condition (if any).
! <literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
! (TRUE)</literal>, that is, no rows are removed by qualification.
! These join types are just a notational convenience, since they
! do nothing you couldn't do with plain <literal>FROM</literal> and
! <literal>WHERE</literal>.
</para>
<para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
--- 621,629 ----
In the absence of parentheses, <literal>JOIN</literal>s nest
left-to-right. In any case <literal>JOIN</literal> binds more
tightly than the commas separating <literal>FROM</literal>-list items.
! All the <literal>JOIN</literal> options are just a notational
! convenience, since they do nothing you couldn't do with plain
! <literal>FROM</literal> and <literal>WHERE</literal>.
</para>
<para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
*************** TABLE [ ONLY ] <replaceable class="param
*** 715,720 ****
--- 705,723 ----
</varlistentry>
<varlistentry>
+ <term><literal>CROSS JOIN</literal></term>
+ <listitem>
+ <para>
+ <literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
+ (TRUE)</literal>, that is, no rows are removed by qualification.
+ They produce a simple Cartesian product, the same result as you get from
+ listing the two tables at the top level of <literal>FROM</literal>,
+ but restricted by the join condition (if any).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>LATERAL</literal></term>
<listitem>
<para>
*************** SELECT * FROM <replaceable class="parame
*** 1754,1761 ****
<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
! FROM distributors d, films f
! WHERE f.did = d.did
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
--- 1757,1763 ----
<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
! FROM distributors d JOIN films f USING (did);
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
