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>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r329 - in trunk: . refman-5.0 refman-5.1 | paul | 14 Nov |
