Skip to content

Commit c2db84f

Browse files
bmomjianbmomjian
committed
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
1 parent ec20545 commit c2db84f

File tree

1 file changed

+24
-22
lines changed

1 file changed

+24
-22
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 24 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,9 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
5959
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
6060
[ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
6161
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
62-
<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> [, ...] ) ]
62+
<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> [, ...] ) }
63+
<replaceable class="parameter">from_item</replaceable> NATURAL <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable>
64+
<replaceable class="parameter">from_item</replaceable> CROSS JOIN <replaceable class="parameter">from_item</replaceable>
6365

6466
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
6567

@@ -552,19 +554,15 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
552554
<listitem>
553555
<para><literal>FULL [ OUTER ] JOIN</literal></para>
554556
</listitem>
555-
<listitem>
556-
<para><literal>CROSS JOIN</literal></para>
557-
</listitem>
558557
</itemizedlist>
559558

560559
For the <literal>INNER</literal> and <literal>OUTER</literal> join types, a
561560
join condition must be specified, namely exactly one of
562-
<literal>NATURAL</literal>, <literal>ON <replaceable
563-
class="parameter">join_condition</replaceable></literal>, or
561+
<literal>ON <replaceable
562+
class="parameter">join_condition</replaceable></literal>,
564563
<literal>USING (<replaceable
565-
class="parameter">join_column</replaceable> [, ...])</literal>.
566-
See below for the meaning. For <literal>CROSS JOIN</literal>,
567-
none of these clauses can appear.
564+
class="parameter">join_column</replaceable> [, ...])</literal>,
565+
or <literal>NATURAL</literal>. See below for the meaning.
568566
</para>
569567

570568
<para>
@@ -575,17 +573,9 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
575573
In the absence of parentheses, <literal>JOIN</literal>s nest
576574
left-to-right. In any case <literal>JOIN</literal> binds more
577575
tightly than the commas separating <literal>FROM</literal>-list items.
578-
</para>
579-
580-
<para><literal>CROSS JOIN</literal> and <literal>INNER JOIN</literal>
581-
produce a simple Cartesian product, the same result as you get from
582-
listing the two tables at the top level of <literal>FROM</literal>,
583-
but restricted by the join condition (if any).
584-
<literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
585-
(TRUE)</literal>, that is, no rows are removed by qualification.
586-
These join types are just a notational convenience, since they
587-
do nothing you couldn't do with plain <literal>FROM</literal> and
588-
<literal>WHERE</literal>.
576+
All the <literal>JOIN</literal> options are just a notational
577+
convenience, since they do nothing you couldn't do with plain
578+
<literal>FROM</literal> and <literal>WHERE</literal>.
589579
</para>
590580

591581
<para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
@@ -654,6 +644,19 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
654644
</listitem>
655645
</varlistentry>
656646

647+
<varlistentry>
648+
<term><literal>CROSS JOIN</literal></term>
649+
<listitem>
650+
<para>
651+
<literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
652+
(TRUE)</literal>, that is, no rows are removed by qualification.
653+
They produce a simple Cartesian product, the same result as you get from
654+
listing the two tables at the top level of <literal>FROM</literal>,
655+
but restricted by the join condition (if any).
656+
</para>
657+
</listitem>
658+
</varlistentry>
659+
657660
<varlistentry>
658661
<term><literal>LATERAL</literal></term>
659662
<listitem>
@@ -1686,8 +1689,7 @@ SELECT * FROM <replaceable class="parameter">name</replaceable>
16861689

16871690
<programlisting>
16881691
SELECT f.title, f.did, d.name, f.date_prod, f.kind
1689-
FROM distributors d, films f
1690-
WHERE f.did = d.did
1692+
FROM distributors d JOIN films f USING (did);
16911693

16921694
title | did | name | date_prod | kind
16931695
-------------------+-----+--------------+------------+----------

0 commit comments

Comments
 (0)