8000 doc: split out the NATURAL/CROSS JOIN in SELECT syntax · postgres/postgres@58d9107 · GitHub
[go: up one dir, main page]
Skip to content

Commit 58d9107

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 cc0e506 commit 58d9107

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

@@ -519,19 +521,15 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
519521
<listitem>
520522
<para><literal>FULL [ OUTER ] JOIN</literal></para>
521523
</listitem>
522-
<listitem>
523-
<para><literal>CROSS JOIN</literal></para>
524-
</listitem>
525524
</itemizedlist>
526525

527526
For the <literal>INNER</literal> and <literal>OUTER</literal> join types, a
528527
join condition must be specified, namely exactly one of
529-
<literal>NATURAL</literal>, <literal>ON <replaceable
530-
class="parameter">join_condition</replaceable></literal>, or
528+
<literal>ON <replaceable
529+
class="parameter">join_condition</replaceable></literal>,
531530
<literal>USING (<replaceable
532-
class="parameter">join_column</replaceable> [, ...])</literal>.
533-
See below for the meaning. For <literal>CROSS JOIN</literal>,
534-
none of these clauses can appear.
531+
class="parameter">join_column</replaceable> [, ...])</literal>,
532+
or <literal>NATURAL</literal>. See below for the meaning.
535533
</para>
536534

537535
<para>
@@ -542,17 +540,9 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
542540
In the absence of parentheses, <literal>JOIN</literal>s nest
543541
left-to-right. In any case <literal>JOIN</literal> binds more
544542
tightly than the commas separating <literal>FROM</literal>-list items.
545-
</para>
546-
547-
<para><literal>CROSS JOIN</literal> and <literal>INNER JOIN</literal>
548-
produce a simple Cartesian product, the same result as you get from
549-
listing the two tables at the top level of <literal>FROM</literal>,
550-
but restricted by the join condition (if any).
551-
<literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
552-
(TRUE)</literal>, that is, no rows are removed by qualification.
553-
These join types are just a notational convenience, since they
554-
do nothing you couldn't do with plain <literal>FROM</literal> and
555-
<literal>WHERE</literal>.
543+
All the <literal>JOIN</literal> options are just a notational
544+
convenience, since they do nothing you couldn't do with plain
545+
<literal>FROM</literal> and <literal>WHERE</literal>.
556546
</para>
557547

558548
<para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
@@ -621,6 +611,19 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
621611
</listitem>
622612
</varlistentry>
623613

614+
<varlistentry>
615+
<term><literal>CROSS JOIN</literal></term>
616+
<listitem>
617+
<para>
618+
<literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
619+
(TRUE)</literal>, that is, no rows are removed by qualification.
620+
They produce a simple Cartesian product, the same result as you get from
621+
listing the two tables at the top level of <literal>FROM</literal>,
622+
but restricted by the join condition (if any).
623+
</para>
624+
</listitem>
625+
</varlistentry>
626+
624627
<varlistentry>
625628
<term><literal>LATERAL</literal></term>
626629
<listitem>
@@ -1653,8 +1656,7 @@ SELECT * FROM <replaceable class="parameter">name</replaceable>
16531656

16541657
<programlisting>
16551658
SELECT f.title, f.did, d.name, f.date_prod, f.kind
1656-
FROM distributors d, films f
1657-
WHERE f.did = d.did
1659+
FROM distributors d JOIN films f USING (did);
16581660

16591661
title | did | name | date_prod | kind
16601662
-------------------+-----+--------------+------------+----------

0 commit comments

Comments
 (0)
0