SELECT documentation
Hi,
The Examples section in the documentation for the SELECT command [1]https://www.postgresql.org/docs/current/sql-select.html
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
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 ] ]
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.
/Joel
[1]: https://www.postgresql.org/docs/current/sql-select.html
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.didI 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.didSELECT 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_itemThis 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 <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
join.difftext/x-diff; charset=us-asciiDownload
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
-------------------+-----+--------------+------------+----------
Bruce Momjian <bruce@momjian.us> writes:
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.
You should not remove the CROSS JOIN mention at l. 604, first because
the references to it just below would become odd, and second because
then it's not explained anywhere on the page. Perhaps you could
put back a definition of CROSS JOIN just below the entry for NATURAL,
but you'll still have to do something with the references at l. 614,
628, 632.
Also, doesn't "[ AS join_using_alias ]" apply to NATURAL and CROSS
joins? You've left that out of the syntax summary.
regards, tom lane
On Sat, Aug 13, 2022 at 10:21:26PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
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.You should not remove the CROSS JOIN mention at l. 604, first because
the references to it just below would become odd, and second because
then it's not explained anywhere on the page. Perhaps you could
put back a definition of CROSS JOIN just below the entry for NATURAL,
but you'll still have to do something with the references at l. 614,
628, 632.
Good point. I restrutured the docs to move CROSS JOIN to a separate
section like NATURAL and adjusted the text, patch attached.
Also, doesn't "[ AS join_using_alias ]" apply to NATURAL and CROSS
joins? You've left that out of the syntax summary.
Uh, I only see it for USING in gram.y:
/* JOIN qualification clauses
* Possibilities are:
* USING ( column list ) [ AS alias ]
* allows only unqualified column names,
* which must match between tables.
* ON expr allows more general qualifications.
*
* We return USING as a two-element List (the first item being a sub-List
* of the common column names, and the second either an Alias item or NULL).
* An ON-expr will not be a List, so it can be told apart that way.
*/
join_qual: USING '(' name_list ')' opt_alias_clause_for_join_using
{
$$ = (Node *) list_make2($3, $5);
}
| ON a_expr
{
$$ = $2;
}
;
...
/*
* The alias clause after JOIN ... USING only accepts the AS ColId spelling,
* per SQL standard. (The grammar could parse the other variants, but they
* don't seem to be useful, and it might lead to parser problems in the
* future.)
*/
opt_alias_clause_for_join_using:
AS ColId
{
$$ = makeNode(Alias);
$$->aliasname = $2;
/* the column name list will be inserted later */
}
| /*EMPTY*/ { $$ = NULL; }
;
which is only used in:
| table_ref join_type JOIN table_ref join_qual
| table_ref JOIN table_ref join_qual
I have updated my private build:
https://momjian.us/tmp/pgsql/sql-select.html
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
join.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
new file mode 100644
index 410c80e..1f9538f
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** SELECT [ ALL | DISTINCT [ ON ( <replacea
*** 59,65 ****
[ 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> ] ]
<phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
--- 59,67 ----
[ 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> <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>
*************** TABLE [ ONLY ] <replaceable class="param
*** 600,618 ****
<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>USING (<replaceable
! class="parameter">join_column</replaceable> [, ...])</literal>.
! See below for the meaning. For <literal>CROSS JOIN</literal>,
! none of these clauses can appear.
</para>
<para>
--- 602,616 ----
<listitem>
<para><literal>FULL [ OUTER ] 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>ON <replaceable
! class="parameter">join_condition</replaceable></literal>,
<literal>USING (<replaceable
! class="parameter">join_column</replaceable> [, ...])</literal>,
! or <literal>NATURAL</literal>. See below for the meaning.
</para>
<para>
*************** TABLE [ ONLY ] <replaceable class="param
*** 623,639 ****
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>.
</para>
<para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
--- 621,629 ----
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.
! 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
*************** TABLE [ ONLY ] <replaceable class="param
*** 715,720 ****
--- 705,723 ----
</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>
<para>
*************** SELECT * FROM <replaceable class="parame
*** 1754,1761 ****
<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
! FROM distributors d, films f
! WHERE f.did = d.did
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
--- 1757,1763 ----
<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
! FROM distributors d JOIN films f USING (did);
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
On Mon, Aug 15, 2022 at 10:53:18PM -0400, Bruce Momjian wrote:
On Sat, Aug 13, 2022 at 10:21:26PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
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.You should not remove the CROSS JOIN mention at l. 604, first because
the references to it just below would become odd, and second because
then it's not explained anywhere on the page. Perhaps you could
put back a definition of CROSS JOIN just below the entry for NATURAL,
but you'll still have to do something with the references at l. 614,
628, 632.Good point. I restrutured the docs to move CROSS JOIN to a separate
section like NATURAL and adjusted the text, patch attached.
Patch applied back to PG 11. PG 10 was different enough and old enough
that I skipped it. This is a big improvement. Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson