Thread: BUG #11325: Documentation Bug / RFE
The following bug has been logged on the website: Bug reference: 11325 Logged by: Thomas Landauer Email address: thomas@landauer.at PostgreSQL version: Unsupported/Unknown Operating system: Irrelevant Description: Two suggestions for "PostgreSQL 9.3.5 Documentation" (which is excellent in general, by the way :-) Capter 7.2.1.1 1) "Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables." Please clarify: All column names that appear in both input tables (regardless of the SELECT clause), or all column names /listed in the SELECT clause/ that appear in both input tables? 2) "with the exception that if ON is used there will be two columns a, b, and c in the result" Hard to understand. Suggestion: "if ON is used, each column a, b, and c will appear twice in the result." Cheers, Thomas
thomas@landauer.at writes:
> Two suggestions for "PostgreSQL 9.3.5 Documentation" (which is excellent in
> general, by the way :-)
> 1) "Finally, NATURAL is a shorthand form of USING: it forms a USING list
> consisting of all column
> names that appear in both input tables."
> Please clarify: All column names that appear in both input tables
> (regardless of the SELECT clause), or all column names /listed in the SELECT
> clause/ that appear in both input tables?
"All column names that appear in both input tables" seems perfectly
clear to me. Where would you get the idea that it had something to
do with the SELECT list?
> 2) "with the exception that if ON is used there will be two columns a, b,
> and c in the result"
> Hard to understand. Suggestion:
> "if ON is used, each column a, b, and c will appear twice in the result."
Yeah, that's fair, though perhaps it would be better as "each of the
columns a, b, and c will appear twice in the result".
regards, tom lane
Tom Lane-2 wrote > thomas@ > writes: >> Two suggestions for "PostgreSQL 9.3.5 Documentation" (which is excellent >> in >> general, by the way :-) > >> 1) "Finally, NATURAL is a shorthand form of USING: it forms a USING list >> consisting of all column >> names that appear in both input tables." >> Please clarify: All column names that appear in both input tables >> (regardless of the SELECT clause), or all column names /listed in the >> SELECT >> clause/ that appear in both input tables? > > "All column names that appear in both input tables" seems perfectly > clear to me. Where would you get the idea that it had something to > do with the SELECT list? > >> 2) "with the exception that if ON is used there will be two columns a, b, >> and c in the result" >> Hard to understand. Suggestion: >> "if ON is used, each column a, b, and c will appear twice in the result." > > Yeah, that's fair, though perhaps it would be better as "each of the > columns a, b, and c will appear twice in the result". Agree on both counts. Though the entire "Thus, USING (a,b,c) ..." seems superfluous given the subsequent example section. And given the 'furthermore' aspect of this it isn't really "shorthand" for ON but it's own unique mechanic and syntax. A comment in its own paragraph to the effect: SELECT * output - ON - all columns from T1 followed by all columns from T2 USING - all join columns, one copy each and in the listed order, followed by non-join columns in T1 followed by non-join columns in T2 could be added while removing the "thus" paragraph in USING; as well as the concept of shorthand. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11325-Documentation-Bug-RFE-tp5817245p5817249.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
David G Johnston wrote
>
> Tom Lane-2 wrote
>> thomas@
>> writes:
>>> Two suggestions for "PostgreSQL 9.3.5 Documentation" (which is excellent
>>> in
>>> general, by the way :-)
>>
>>> 1) "Finally, NATURAL is a shorthand form of USING: it forms a USING list
>>> consisting of all column
>>> names that appear in both input tables."
>>> Please clarify: All column names that appear in both input tables
>>> (regardless of the SELECT clause), or all column names /listed in the
>>> SELECT
>>> clause/ that appear in both input tables?
>>
>> "All column names that appear in both input tables" seems perfectly
>> clear to me. Where would you get the idea that it had something to
>> do with the SELECT list?
>>
>>> 2) "with the exception that if ON is used there will be two columns a,
>>> b,
>>> and c in the result"
>>> Hard to understand. Suggestion:
>>> "if ON is used, each column a, b, and c will appear twice in the
>>> result."
>>
>> Yeah, that's fair, though perhaps it would be better as "each of the
>> columns a, b, and c will appear twice in the result".
> Agree on both counts.
>
> Though the entire "Thus, USING (a,b,c) ..." seems superfluous given the
> subsequent example section. And given the 'furthermore' aspect of this it
> isn't really "shorthand" for ON but it's own unique mechanic and syntax.
>
> A comment in its own paragraph to the effect:
>
> SELECT * output -
> ON - all columns from T1 followed by all columns from T2
> USING - all join columns, one copy each and in the listed order, followed
> by non-join columns in T1 followed by non-join columns in T2
>
> could be added while removing the "thus" paragraph in USING; as well as
> the concept of shorthand.
>
> David J.
Usage note for USING/NATURAL added as well.
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 9bf3136..c53c2b0 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -245,25 +245,38 @@ FROM <replaceable>table_reference</replaceable>
<optional>, <replaceable>table_r
condition: it takes a Boolean value expression of the same
kind as is used in a <literal>WHERE</> clause. A pair of rows
from <replaceable>T1</> and <replaceable>T2</> match if the
- <literal>ON</> expression evaluates to true for them.
+ <literal>ON</> expression evaluates to true.
</para>
<para>
- <literal>USING</> is a shorthand notation: it takes a
- comma-separated list of column names, which the joined tables
- must have in common, and forms a join condition specifying
- equality of each of these pairs of columns. Furthermore, the
- output of <literal>JOIN USING</> has one column for each of
- the equated pairs of input columns, followed by the
- remaining columns from each table. Thus, <literal>USING (a, b,
- c)</literal> is equivalent to <literal>ON (t1.a = t2.a AND
- t1.b = t2.b AND t1.c = t2.c)</literal> with the exception that
- if <literal>ON</> is used there will be two columns
- <literal>a</>, <literal>b</>, and <literal>c</> in the result,
- whereas with <literal>USING</> there will be only one of each
- (and they will appear first if <command>SELECT *</> is used).
+ The <literal>USING</> clause allows you to take advantage of
+ the specific situation where both sides of the join use the
+ same name for the joining columns. It takes a
+ comma-separated list of the shared column names
+ and forms a join using the equals operator. Furthermore, the
+ output of <literal>JOIN USING</> has one column for each of the
+ listed columns, followed by the remaining columns from each table.
</para>
+ <para>The output column difference between <literal>ON</> and
<literal>USING</> when invoking <literal>SELECT *</> is:</para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>ON</> - all columns from T1 followed by all columns
from T2
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>USING</> - all join columns, one copy each and in the
listed order, followed by non-join columns in <replaceable>T1</> followed by
non-join columns in <replaceable>T2</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Examples provided below
+ </para>
+ </listitem>
+ </itemizedlist>
+
<para>
<indexterm>
<primary>join</primary>
@@ -282,6 +295,16 @@ FROM <replaceable>table_reference</replaceable>
<optional>, <replaceable>table_r
</para>
<para>
+ <emphasis>Usage Note:</>
+ <literal>USING</literal> is reasonably safe from column changes
+ in the joined relations since only the specific columns mentioned
+ are considered. <literal>NATURAL</> is considerably more
problematic
+ if you are referring to relations only by name (views and tables)
+ since any schema changes to either relation that cause a new
matching
+ column name to be present will cause the join to consider that new
column.
+ </para>
+
+ <para>
The possible types of qualified join are:
<variablelist>
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11325-Documentation-Bug-RFE-tp5817245p5817259.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Converted the usage note to an actual <note> tag and added a couple of
missing <replaceable> tags for T1 and T2
David J.
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 9bf3136..e640d29 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -245,25 +245,38 @@ FROM <replaceable>table_reference</replaceable>
<optional>, <replaceable>table_r
condition: it takes a Boolean value expression of the same
kind as is used in a <literal>WHERE</> clause. A pair of rows
from <replaceable>T1</> and <replaceable>T2</> match if the
- <literal>ON</> expression evaluates to true for them.
+ <literal>ON</> expression evaluates to true.
</para>
<para>
- <literal>USING</> is a shorthand notation: it takes a
- comma-separated list of column names, which the joined tables
- must have in common, and forms a join condition specifying
- equality of each of these pairs of columns. Furthermore, the
- output of <literal>JOIN USING</> has one column for each of
- the equated pairs of input columns, followed by the
- remaining columns from each table. Thus, <literal>USING (a, b,
- c)</literal> is equivalent to <literal>ON (t1.a = t2.a AND
- t1.b = t2.b AND t1.c = t2.c)</literal> with the exception that
- if <literal>ON</> is used there will be two columns
- <literal>a</>, <literal>b</>, and <literal>c</> in the result,
- whereas with <literal>USING</> there will be only one of each
- (and they will appear first if <command>SELECT *</> is used).
+ The <literal>USING</> clause allows you to take advantage of
+ the specific situation where both sides of the join use the
+ same name for the joining columns. It takes a
+ comma-separated list of the shared column names
+ and forms a join using the equals operator. Furthermore, the
+ output of <literal>JOIN USING</> has one column for each of the
+ listed columns, followed by the remaining columns from each table.
</para>
+ <para>The output column difference between <literal>ON</> and
<literal>USING</> when invoking <literal>SELECT *</> is:</para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>ON</> - all columns from <replaceable>T1</> followed
by all columns from <replaceable>T2</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>USING</> - all join columns, one copy each and in the
listed order, followed by non-join columns in <replaceable>T1</> followed
by non-join columns in <replaceable>T2</>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Examples provided below
+ </para>
+ </listitem>
+ </itemizedlist>
+
<para>
<indexterm>
<primary>join</primary>
@@ -281,6 +294,17 @@ FROM <replaceable>table_reference</replaceable>
<optional>, <replaceable>table_r
<literal>CROSS JOIN</literal>.
</para>
+ <note>
+ <para>
+ <literal>USING</literal> is reasonably safe from column changes
+ in the joined relations since only the specific columns mentioned
+ are considered. <literal>NATURAL</> is considerably more
problematic
+ if you are referring to relations only by name (views and tables)
+ since any schema changes to either relation that cause a new
matching
+ column name to be present will cause the join to consider that
new column.
+ </para>
+ </note>
+
<para>
The possible types of qualified join are:
On Mon, Sep 1, 2014 at 7:54 PM, David G Johnston [via PostgreSQL] <
ml-node+s1045698n5817259h53@n5.nabble.com> wrote:
> David G Johnston wrote
>
> Tom Lane-2 wrote
> [hidden email] <http://user/SendEmail.jtp?type=node&node=5817259&i=0> writes:
>
> > Two suggestions for "PostgreSQL 9.3.5 Documentation" (which is excellent
> in
> > general, by the way :-)
>
> > 1) "Finally, NATURAL is a shorthand form of USING: it forms a USING list
> > consisting of all column
> > names that appear in both input tables."
> > Please clarify: All column names that appear in both input tables
> > (regardless of the SELECT clause), or all column names /listed in the
> SELECT
> > clause/ that appear in both input tables?
>
> "All column names that appear in both input tables" seems perfectly
> clear to me. Where would you get the idea that it had something to
> do with the SELECT list?
>
> > 2) "with the exception that if ON is used there will be two columns a,
> b,
> > and c in the result"
> > Hard to understand. Suggestion:
> > "if ON is used, each column a, b, and c will appear twice in the
> result."
>
> Yeah, that's fair, though perhaps it would be better as "each of the
> columns a, b, and c will appear twice in the result".
>
> Agree on both counts.
>
> Though the entire "Thus, USING (a,b,c) ..." seems superfluous given the
> subsequent example section. And given the 'furthermore' aspect of this it
> isn't really "shorthand" for ON but it's own unique mechanic and syntax.
>
> A comment in its own paragraph to the effect:
>
> SELECT * output -
> ON - all columns from T1 followed by all columns from T2
> USING - all join columns, one copy each and in the listed order, followed
> by non-join columns in T1 followed by non-join columns in T2
>
> could be added while removing the "thus" paragraph in USING; as well as
> the concept of shorthand.
>
> David J.
>
> Usage note for USING/NATURAL added as well.
>
>
>
>
> diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
> index 9bf3136..c53c2b0 100644
> --- a/doc/src/sgml/queries.sgml
> +++ b/doc/src/sgml/queries.sgml
> @@ -245,25 +245,38 @@ FROM
> <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
> condition: it takes a Boolean value expression of the same
> kind as is used in a <literal>WHERE</> clause. A pair of rows
> from <replaceable>T1</> and <replaceable>T2</> match if the
> - <literal>ON</> expression evaluates to true for them.
> + <literal>ON</> expression evaluates to true.
> </para>
>
> <para>
> - <literal>USING</> is a shorthand notation: it takes a
> - comma-separated list of column names, which the joined tables
> - must have in common, and forms a join condition specifying
> - equality of each of these pairs of columns. Furthermore, the
> - output of <literal>JOIN USING</> has one column for each of
> - the equated pairs of input columns, followed by the
> - remaining columns from each table. Thus, <literal>USING (a, b,
> - c)</literal> is equivalent to <literal>ON (t1.a = t2.a AND
> - t1.b = t2.b AND t1.c = t2.c)</literal> with the exception that
> - if <literal>ON</> is used there will be two columns
> - <literal>a</>, <literal>b</>, and <literal>c</> in the result,
> - whereas with <literal>USING</> there will be only one of each
> - (and they will appear first if <command>SELECT *</> is used).
> + The <literal>USING</> clause allows you to take advantage of
> + the specific situation where both sides of the join use the
> + same name for the joining columns. It takes a
> + comma-separated list of the shared column names
> + and forms a join using the equals operator. Furthermore, the
> + output of <literal>JOIN USING</> has one column for each of the
> + listed columns, followed by the remaining columns from each
> table.
> </para>
>
> + <para>The output column difference between <literal>ON</> and
> <literal>USING</> when invoking <literal>SELECT *</> is:</para>
> + <itemizedlist>
> + <listitem>
> + <para>
> + <literal>ON</> - all columns from T1 followed by all columns
> from T2
> + </para>
> + </listitem>
> + <listitem>
> + <para>
> + <literal>USING</> - all join columns, one copy each and in
> the listed order, followed by non-join columns in
> <replaceable>T1</> followed by non-join columns in <replaceable>T2</>
> + </para>
> + </listitem>
> + <listitem>
> + <para>
> + Examples provided below
> + </para>
> + </listitem>
> + </itemizedlist>
> +
> <para>
> <indexterm>
> <primary>join</primary>
> @@ -282,6 +295,16 @@ FROM
> <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
> </para>
>
> <para>
> + <emphasis>Usage Note:</>
> + <literal>USING</literal> is reasonably safe from column changes
> + in the joined relations since only the specific columns mentioned
> + are considered. <literal>NATURAL</> is considerably more
> problematic
> + if you are referring to relations only by name (views and tables)
> + since any schema changes to either relation that cause a new
> matching
> + column name to be present will cause the join to consider that
> new column.
> + </para>
> +
> + <para>
> The possible types of qualified join are:
>
> <variablelist>
>
>
>
>
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/BUG-11325-Documentation-Bug-RFE-tp5817245p5817259.html
> To unsubscribe from BUG #11325: Documentation Bug / RFE, click here
>
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5817245&code=ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb218NTgxNzI0NXwtMzI2NTA0MzIx>
> .
> NAML
>
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
queries_7-2-1-1_v1a.diff (4K) <http://postgresql.1045698.n5.nabble.com/attachment/5817262/0/queries_7-2-1-1_v1a.diff>
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11325-Documentation-Bug-RFE-tp5817245p5817262.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Mon, Sep 1, 2014 at 04:54:15PM -0700, David G Johnston wrote: > > A comment in its own paragraph to the effect: > > > > SELECT * output - > > ON - all columns from T1 followed by all columns from T2 > > USING - all join columns, one copy each and in the listed order, followed > > by non-join columns in T1 followed by non-join columns in T2 > > > > could be added while removing the "thus" paragraph in USING; as well as > > the concept of shorthand. > > > > David J. > > Usage note for USING/NATURAL added as well. Patch applied, with line wrap adjustments. Thanks. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +