svn commit - mysqldoc@docsrva: r329 - in trunk: . refman-5.0 refman-5.1

[paul]

Author: paul
Date: 2005-11-14 17:49:27 +0100 (Mon, 14 Nov 2005)
New Revision: 329

Log:
 r3694@frost:  paul | 2005-11-14 09:05:05 -0600
 Another join/SQL:2003 change: ON and USING no longer are
 semantically identical.


Modified:
   trunk/
   trunk/refman-5.0/sql-syntax.xml
   trunk/refman-5.1/sql-syntax.xml


Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:3687
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:958
   + b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:3694
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:958

Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml	2005-11-13 19:37:57 UTC (rev 328)
+++ trunk/refman-5.0/sql-syntax.xml	2005-11-14 16:49:27 UTC (rev 329)
@@ -7159,13 +7159,15 @@
               The
              
<literal>USING(<replaceable>column_list</replaceable>)</literal>
               clause names a list of columns that must exist in both
-              tables. The following two clauses are semantically
-              identical:
+              tables. If tables <literal>a</literal> and
+              <literal>b</literal> both contain columns
+              <literal>c1</literal>, <literal>c2</literal>, and
+              <literal>c3</literal>, the following join compares
+              corresponding columns from the two tables:
             </para>
 
 <programlisting>
 a LEFT JOIN b USING (c1,c2,c3)
-a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
 </programlisting>
           </listitem>
 
@@ -7568,6 +7570,75 @@
 </programlisting>
           </listitem>
 
+          <listitem>
+            <para>
+              Before MySQL 5.0.12, a <literal>USING</literal> clause can
+              be rewritten as an <literal>ON</literal> clause that
+              compares corresponding columns. For example, the following
+              two clauses are semantically identical:
+            </para>
+
+<programlisting>
+a LEFT JOIN b USING (c1,c2,c3)
+a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
+</programlisting>
+
+            <para>
+              From 5.0.12 on, the two clauses no longer are quite the
+              same:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  With respect to determining which rows satisfy the
+                  join condition, both joins remain semantically
+                  identical.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  With respect to determining which columns to display
+                  for <literal>SELECT *</literal> expansion, the two
+                  joins are not semantically identical. The
+                  <literal>USING</literal> join selects the coalesced
+                  value of correspondinging columns, whereas the
+                  <literal>ON</literal> join selects all columns from
+                  all tables. For the preceding <literal>USING</literal>
+                  join, <literal>SELECT *</literal> selects these
+                  values:
+                </para>
+
+<programlisting>
+COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
+</programlisting>
+
+                <para>
+                  For the <literal>ON</literal> join, <literal>SELECT
+                  *</literal> selects these values:
+                </para>
+
+<programlisting>
+a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
+</programlisting>
+
+                <para>
+                  With an inner join,
+                  <literal>COALESCE(a.c1,b.c1)</literal> is the same as
+                  either <literal>a.c1</literal> or
+                  <literal>b.c1</literal> because both columns will have
+                  the same value. With an outer join (such as
+                  <literal>LEFT JOIN</literal>), one of the two columns
+                  can be <literal>NULL</literal>. That column will be
+                  omitted from the result.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+          </listitem>
+
         </itemizedlist>
 
       </section>

Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml	2005-11-13 19:37:57 UTC (rev 328)
+++ trunk/refman-5.1/sql-syntax.xml	2005-11-14 16:49:27 UTC (rev 329)
@@ -7717,13 +7717,15 @@
               The
              
<literal>USING(<replaceable>column_list</replaceable>)</literal>
               clause names a list of columns that must exist in both
-              tables. The following two clauses are semantically
-              identical:
+              tables. If tables <literal>a</literal> and
+              <literal>b</literal> both contain columns
+              <literal>c1</literal>, <literal>c2</literal>, and
+              <literal>c3</literal>, the following join compares
+              corresponding columns from the two tables:
             </para>
 
 <programlisting>
 a LEFT JOIN b USING (c1,c2,c3)
-a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
 </programlisting>
           </listitem>
 
@@ -8126,6 +8128,74 @@
 </programlisting>
           </listitem>
 
+          <listitem>
+            <para>
+              In older versions, a <literal>USING</literal> clause can
+              be rewritten as an <literal>ON</literal> clause that
+              compares corresponding columns. For example, the following
+              two clauses are semantically identical:
+            </para>
+
+<programlisting>
+a LEFT JOIN b USING (c1,c2,c3)
+a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
+</programlisting>
+
+            <para>
+              Now the two clauses no longer are quite the same:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  With respect to determining which rows satisfy the
+                  join condition, both joins remain semantically
+                  identical.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  With respect to determining which columns to display
+                  for <literal>SELECT *</literal> expansion, the two
+                  joins are not semantically identical. The
+                  <literal>USING</literal> join selects the coalesced
+                  value of correspondinging columns, whereas the
+                  <literal>ON</literal> join selects all columns from
+                  all tables. For the preceding <literal>USING</literal>
+                  join, <literal>SELECT *</literal> selects these
+                  values:
+                </para>
+
+<programlisting>
+COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
+</programlisting>
+
+                <para>
+                  For the <literal>ON</literal> join, <literal>SELECT
+                  *</literal> selects these values:
+                </para>
+
+<programlisting>
+a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
+</programlisting>
+
+                <para>
+                  With an inner join,
+                  <literal>COALESCE(a.c1,b.c1)</literal> is the same as
+                  either <literal>a.c1</literal> or
+                  <literal>b.c1</literal> because both columns will have
+                  the same value. With an outer join (such as
+                  <literal>LEFT JOIN</literal>), one of the two columns
+                  can be <literal>NULL</literal>. That column will be
+                  omitted from the result.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+          </listitem>
+
         </itemizedlist>
 
       </section>