Re: BUG #11325: Documentation Bug / RFE

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.