Re: BUG #11325: Documentation Bug / RFE - Mailing list pgsql-bugs

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.