On Thu, Dec 30, 2021 at 12:11:26AM +0100, Joel Jacobson wrote:
> Hi,
>
> The Examples section in the documentation for the SELECT command [1]
> only contains a single example on how to join two tables,
> which is written in SQL-89 style:
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d, films f
> WHERE f.did = d.did
>
> I think it's good to keep this example query as it is,
> and suggest we add the following equivalent queries:
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> JOIN films f ON f.did = d.did
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> JOIN films f USING (did)
>
> SELECT f.title, f.did, d.name, f.date_prod, f.kind
> FROM distributors d
> NATURAL JOIN films f
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.
Should we link to the join docs?
https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM
I didn't see anything additional there that would warrant a link.
> I also think it would be an improvement to break up the from_item below into
> three separate items,
> since the optional NATURAL cannot occur in combination with ON nor USING.
>
> from_item [ NATURAL ] join_type from_item [ ON join_condition | USING (
> join_column [, ...] ) [ AS join_using_alias ] ]
Agreed. I am surprised this has stayed like this for so long --- it is
confusing.
> Suggestion:
>
> from_item join_type from_item ON join_condition
> from_item join_type from_item USING ( join_column [, ...] ) [ AS
> join_using_alias ]
> from_item NATURAL join_type from_item
>
> This would be more readable imo.
> I picked the order ON, USING, NATURAL to match the order they are described in
> the FROM Clause section.
I went a different direction, since I was fine with ON/USING being a
choice, rather than optional. Also, CROSS JOIN can't use a join_type,
so I split the one line into three in the attached patch, and verified
this from gram.y. Our join docs have this clearly shown:
https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM
from_item join_type from_item { ON join_condition | USING ( join_column [,
...] ) [ AS join_using_alias ] }
from_item NATURAL join_type from_item
from_item CROSS JOIN from_item
but for some reason SELECT had them all mashed together. Should I
split ON/USING on separate lines?
You can see the result here:
https://momjian.us/tmp/pgsql/sql-select.html
--
Bruce Momjian <br...@momjian.us> 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
index 410c80e730..bcdfa1d186 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> [, ...] ) [ AS <replaceable class="parameter">join_using_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>
@@ -600,9 +602,6 @@ 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
@@ -1754,8 +1753,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
-------------------+-----+--------------+------------+----------
