doc: split out the NATURAL/CROSS JOIN in SELECT syntax

This allows the syntax to be more accurate about what clauses are
supported.  Also switch an example query to use the ANSI join syntax.

Reported-by: Joel Jacobson
Discussion: https://postgr.es/m/67b71d3e-0c22-44df-a223-351f14418319@www.fastmail.com

Backpatch-through: 11

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 1e0f09e6623fbdc2f96398e5c7f7f37b19db1f4f..0e48cc846541539c02fae16a165a344fc3067ae8 100644 (file)
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -59,7 +59,9 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ 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> [, ...] ) ]
+    <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> [, ...] ) }
+    <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>
 
@@ -519,19 +521,15 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
          <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>ON <replaceable
+        class="parameter">join_condition</replaceable></literal>,
         <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.
+        class="parameter">join_column</replaceable> [, ...])</literal>,
+        or <literal>NATURAL</literal>.  See below for the meaning.
        </para>
 
        <para>
@@ -542,17 +540,9 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
         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>.
+        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
@@ -621,6 +611,19 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
       </listitem>
      </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>
@@ -1653,8 +1656,7 @@ SELECT * FROM <replaceable class="parameter">name</replaceable>
 
 <programlisting>
 SELECT f.title, f.did, d.name, f.date_prod, f.kind
-    FROM distributors d, films f
-    WHERE f.did = d.did
+    FROM distributors d JOIN films f USING (did);
 
        title       | did |     name     | date_prod  |   kind
 -------------------+-----+--------------+------------+----------