postgres.git / summary / log / commit / refs
commit 181cc0906b9130f0acb37131a50c06a601ec115c
Author: Bruce Momjian <bruce@momjian.us>
Date: Thu Sep 01 01:46:14 2022 +0000
First released in: 16.0, 11.18, 12.13, 13.9, 14.6, 15.0
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
doc/src/sgml/ref/select.sgml | 46 +++++++++++++++++++++++---------------------
1 file changed, 24 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index aa823d2ce77..3af337a65cb 100644
--- 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>
@@ -554,19 +556,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>
@@ -577,17 +575,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
@@ -656,6 +646,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>
@@ -1693,8 +1696,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
-------------------+-----+--------------+------------+----------
[parent: b6fe152fc27a]