db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j..@apache.org
Subject svn commit: r189592 - in /incubator/derby/docs/trunk/src: devguide/cdevstandards806001.dita ref/DitaLink.cat ref/rrefselectexpression.dita ref/rrefso2.dita ref/rrefsql9241891.dita ref/rrefsqlj1083019.dita ref/rrefsqlj21571.dita ref/rrefsqlj41360.dita tuning/ctuntransform14044.dita
Date Wed, 08 Jun 2005 15:29:13 GMT
Author: jta
Date: Wed Jun  8 08:29:12 2005
New Revision: 189592

URL: http://svn.apache.org/viewcvs?rev=189592&view=rev
Log:
DERBY-278: Committed Jeff Levitt's patch that adds documentation for 
INTERSECT and EXCEPT.

Modified:
    incubator/derby/docs/trunk/src/devguide/cdevstandards806001.dita
    incubator/derby/docs/trunk/src/ref/DitaLink.cat
    incubator/derby/docs/trunk/src/ref/rrefselectexpression.dita
    incubator/derby/docs/trunk/src/ref/rrefso2.dita
    incubator/derby/docs/trunk/src/ref/rrefsql9241891.dita
    incubator/derby/docs/trunk/src/ref/rrefsqlj1083019.dita
    incubator/derby/docs/trunk/src/ref/rrefsqlj21571.dita
    incubator/derby/docs/trunk/src/ref/rrefsqlj41360.dita
    incubator/derby/docs/trunk/src/tuning/ctuntransform14044.dita

Modified: incubator/derby/docs/trunk/src/devguide/cdevstandards806001.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/devguide/cdevstandards806001.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/devguide/cdevstandards806001.dita (original)
+++ incubator/derby/docs/trunk/src/devguide/cdevstandards806001.dita Wed Jun  8 08:29:12 2005
@@ -32,6 +32,6 @@
 <li>MIN aggregate function</li>
 <li>MAX aggregate function</li>
 <li>[NOT] IN predicate</li>
-<li>UNION operator</li>
+<li>UNION, INTERSECT, and EXCEPT operators</li>
 </ul>SQL99 also places some restrictions on expressions on LONG types.</p>
 </conbody></concept>

Modified: incubator/derby/docs/trunk/src/ref/DitaLink.cat
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/DitaLink.cat?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/DitaLink.cat (original)
+++ incubator/derby/docs/trunk/src/ref/DitaLink.cat Wed Jun  8 08:29:12 2005
@@ -1,3 +1,3 @@
-# (v2.0) Dita Link Catalog for: C:/cloudscape/reference/dita/final updated: Fri Mar 11 15:01:53
2005
-*MAP0|C:/cloudscape/reference/dita/final
-*MAP1|C:/cloudscape/reference/dita/final
+# (v2.0) Dita Link Catalog for: C:/derby/intersect/trunk/src/ref updated: Tue Jun 07 16:50:42
2005
+*MAP0|C:/derby/intersect/trunk/src/ref
+*MAP1|C:/derby/intersect/trunk/src/ref

Modified: incubator/derby/docs/trunk/src/ref/rrefselectexpression.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefselectexpression.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefselectexpression.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefselectexpression.dita Wed Jun  8 08:29:12 2005
@@ -87,9 +87,9 @@
 all columns in the identified table. That table must be listed in the associated
 FROM clause.</p> </section>
 <section><title>Naming columns</title> <p>You can name a <i>SelectItem</i>
column
-using the AS clause. When the <i>SelectExpression</i> appears in a UNION,
+using the AS clause. When the <i>SelectExpression</i> appears in a UNION, INTERSECT,
or EXCEPT operator, 
 the names from the first <i>SelectExpression</i> are taken as the names for
-the columns in the result of the UNION. If a column of a <i>SelectItem</i> is
+the columns in the result of the operation. If a column of a <i>SelectItem</i>
is
 not a simple <i>ColumnReference</i> expression or named with an AS clause,
 it is given a generated unique name.</p> <p>These column names are useful
 in several cases:   <ul>

Modified: incubator/derby/docs/trunk/src/ref/rrefso2.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefso2.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefso2.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefso2.dita Wed Jun  8 08:29:12 2005
@@ -36,7 +36,7 @@
 <section><p>Errors can happen when a <i>ResultSet</i> is first being
created
 if the system partially executes the query before the first row is fetched.
 This can happen on any query that uses more than one table and on queries
-that use aggregates, GROUP BY, ORDER BY, DISTINCT, or UNION.</p></section>
+that use aggregates, GROUP BY, ORDER BY, DISTINCT, INTERSECT, EXCEPT, or UNION.</p></section>
 <section><p>Closing a <i>Statement</i> causes all open <i>ResultSet</i>
objects
 on that statement to be closed as well.</p></section>
 <section><p>The cursor name for the cursor of a <i>ResultSet</i>
can be set

Modified: incubator/derby/docs/trunk/src/ref/rrefsql9241891.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsql9241891.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsql9241891.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsql9241891.dita Wed Jun  8 08:29:12 2005
@@ -34,9 +34,8 @@
 <li>SQL92I   <p>Intermediate</p></li>
 <li>SQL92F   <p>Full</p></li>
 </ul>  <table frame="all" id="sql921038347"><title>Support for SQL-92 Features</title>
-<tgroup cols="4" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="4*"/>
-<colspec colname="2" colnum="2" colwidth="40*"/><colspec colname="3" colnum="3"
-colwidth="25*"/><colspec colname="4" colnum="4" colwidth="31*"/>
+<tgroup cols="4" colsep="1" rowsep="1"><colspec colname="1" colnum="1"/>
+<colspec colname="2" colnum="2"/><colspec colname="3" colnum="3"/><colspec
colname="4" colnum="4"/>
 <thead>
 <row>
 <entry align="left" nameend="2" namest="1" valign="bottom">Feature</entry>
@@ -421,7 +420,7 @@
 <entry colname="4">yes</entry>
 </row>
 <row>
-<entry nameend="2" namest="1">UNION in views</entry>
+<entry nameend="2" namest="1">UNION, INTERSECT, and EXCEPT in views</entry>
 <entry colname="3">SQL92T</entry>
 <entry colname="4">yes</entry>
 </row>

Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj1083019.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj1083019.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj1083019.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj1083019.dita Wed Jun  8 08:29:12 2005
@@ -36,7 +36,7 @@
 parameters in the list.   <codeblock><b>WHERE ? NOT IN (?, ?, 'Santiago')
 <ph>-- types assumed to be CHAR</ph></b></codeblock></li>
 <li>Use in the values list in an IN predicate is allowed if the first operand
-is not a dynamic parameter or its type was determined in rule <xref href="rrefsqlj1083019.dita#rrefsqlj1083019/sqlj30695"></xref>.
+is not a dynamic parameter or its type was determined in the previous rule.
 Type of the dynamic parameters appearing in the values list is assumed to
 be the type of the left operand.   <codeblock><b>WHERE <b><i>FloatColumn</i></b>
IN (?, ?, ?)
 <ph>-- types assumed to be FLOAT</ph></b></codeblock></li>
@@ -79,7 +79,7 @@
 list of an INSERT statement. The type of the dynamic parameter is assumed
 to be the type of the target column. A ? parameter is not allowed by itself
 in any select list, including the select list of a subquery, unless there
-is a corresponding column in a UNION (see no. <xref href="rrefsqlj1083019.dita#rrefsqlj1083019/sqlj20756"></xref>,
+is a corresponding column in a UNION, INTERSECT, or EXCEPT (see no. <xref href="rrefsqlj1083019.dita#rrefsqlj1083019/sqlj20756">16</xref>,
 below) that is not dynamic.   <codeblock><b>INSERT INTO t VALUES (?)
 <ph>-- dynamic parameter assumed to be the type
 -- of the only column in table t</ph>
@@ -108,8 +108,8 @@
 1 = SOME (SELECT ? FROM t)
 <ph>-- is valid. Dynamic parameter assumed to be INTEGER type.</ph></b></codeblock></li>
 <li id="sqlj20756">A dynamic parameter is allowed to represent a column if
-it appears in a UNION expression; <ph conref="refconrefs.dita#prod/productshortname"></ph>
can
-infer the data type from the corresponding column in the UNION.   <codeblock><b>SELECT
?
+it appears in a UNION, INTERSECT, or EXCEPT expression; <ph conref="refconrefs.dita#prod/productshortname"></ph>
can
+infer the data type from the corresponding column in the expression.   <codeblock><b>SELECT
?
 FROM t
 UNION SELECT 1
 FROM t

Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj21571.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj21571.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj21571.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj21571.dita Wed Jun  8 08:29:12 2005
@@ -1,4 +1,6 @@
 <?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
 <!-- 
 Copyright 1997, 2004 The Apache Software Foundation or its licensors, as applicable.  
 
@@ -14,28 +16,44 @@
 See the License for the specific language governing permissions and  
 limitations under the License.
 -->
-
-<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
- "../dtd/reference.dtd">
 <reference id="rrefsqlj21571" xml:lang="en-us">
 <title>Query</title>
+<prolog><metadata>
+<keywords><indexterm>UNION</indexterm><indexterm>UNION ALL</indexterm></keywords>
+</metadata></prolog>
 <refbody>
 <section> <p>A query creates a virtual table based on existing tables or constants
 built into tables.</p></section>
-<refsyn><title>Syntax</title> <codeblock><b>{
+<refsyn><title>Syntax</title><codeblock><b>{
     ( <i>Query</i> ) |
-<i>    Query</i> UNION [ALL] <i>Query</i> |
+<i>    Query</i> INTERSECT [ ALL | DISTINCT ] <i>Query</i> |
+<i>    Query</i> EXCEPT [ ALL | DISTINCT ] <i>Query</i> |
+<i>    Query</i> UNION [ ALL | DISTINCT ] <i>Query</i> |
 <i>    <xref href="rrefselectexpression.dita#rrefselectexpression"></xref></i>
 <i>    </i><xref href="rrefsqlj11277.dita#rrefsqlj11277"></xref>
-}</b></codeblock> <p><indexterm>UNION</indexterm><indexterm>UNION
ALL</indexterm>You
-can arbitrarily put parentheses around queries, or use the parentheses to
-control the order of evaluation of UNION operations. UNION operations are
-evaluated from left to right when no parentheses are present.</p> <p>You can
-combine two queries into one using the UNION [ALL] operation. UNION builds
-an intermediate <i>ResultSet</i> with all of the rows from both queries and
-eliminates the duplicate rows before returning the remaining rows. UNION ALL
-returns all rows from both queries as the result.</p> </refsyn>
-<example> <codeblock><b><ph>-- a Select expression </ph>
+}</b></codeblock><p>You can arbitrarily put parentheses around queries,
or
+use the parentheses to control the order of evaluation of the INTERSECT, EXCEPT,
+or UNION operations. These operations are evaluated from left to right when
+no parentheses are present, with the exception of INTERSECT operations, which
+would be evaluated before any UNION or EXCEPT operations.</p></refsyn>
+<section><title>Duplicates in UNION, INTERSECT, and EXCEPT ALL results</title><p>The
+ALL and DISTINCT keywords determine whether duplicates are eliminated from
+the result of the operation. If you specify the DISTINCT keyword, then the
+result will have no duplicate rows. If you specify the ALL keyword, then there
+may be duplicates in the result, depending on whether there were duplicates
+in the input.  DISTINCT is the default, so if you don't specify ALL or DISTINCT,
+the duplicates will be eliminated.  For example, UNION builds an intermediate <i>ResultSet</i>
with
+all of the rows from both queries and eliminates the duplicate rows before
+returning the remaining rows. UNION ALL returns all rows from both queries
+as the result.</p><p>Depending on which operation is specified, if the number
+of copies of a row in the left table is L and the number of copies of that
+row in the right table is R, then the number of duplicates of that particular
+row that the output table contains is:<ul>
+<li>UNION: ( L + R ).</li>
+<li>EXCEPT: the maximum of ( L – R ) and 0 (zero).</li>
+<li>INTERSECT: the minimum of L and R.</li>
+</ul></p></section>
+<example><title>Examples</title><codeblock><b><ph>--
a Select expression </ph>
 SELECT *
 FROM FROM ORG;
 
@@ -105,4 +123,3 @@
      VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new');</b></codeblock> </example>
 </refbody>
 </reference>
-

Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj41360.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj41360.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj41360.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj41360.dita Wed Jun  8 08:29:12 2005
@@ -27,7 +27,7 @@
 <section> <p>A SELECT statement consists of a query with an optional <xref
 href="rrefsqlj13658.dita#rrefsqlj13658"></xref> and an optional <xref href="rrefsqlj31783.dita#rrefsqlj31783"></xref>.
 The SELECT statement is so named because the typical first word of the query
-construct is SELECT. (<i>Query</i> includes the VALUES expression and UNION
+construct is SELECT. (<i>Query</i> includes the VALUES expression and UNION,
INTERSECT, and EXCEPT
 expressions as well as SELECT expressions).</p> <p>The <xref href="rrefsqlj13658.dita#rrefsqlj13658"></xref>
guarantees
 the ordering of the <i>ResultSet</i>. The <xref href="rrefsqlj31783.dita#rrefsqlj31783"></xref>
makes
 the result an updatable cursor. The SELECT statement supports the FOR FETCH

Modified: incubator/derby/docs/trunk/src/tuning/ctuntransform14044.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/tuning/ctuntransform14044.dita?rev=189592&r1=189591&r2=189592&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/tuning/ctuntransform14044.dita (original)
+++ incubator/derby/docs/trunk/src/tuning/ctuntransform14044.dita Wed Jun  8 08:29:12 2005
@@ -26,8 +26,8 @@
 <conbody>
 <p>Without a transformation, a statement that contains both ORDER BY and UNION
 would require two separate sorting steps-one to satisfy ORDER BY and
-one to satisfy UNION. (Currently <ph conref="tunconrefs.dita#prod/productshortname"></ph>
uses sorting to eliminate duplicates
-from a UNION.)</p>
+one to satisfy UNION (Currently <ph conref="tunconrefs.dita#prod/productshortname"></ph>
uses sorting to eliminate duplicates
+from a UNION.  You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates.
 So you only use UNION ALL to avoid sorting if you know that there are no duplicate rows in
the tables).</p>
 <p>In some situations, <ph conref="tunconrefs.dita#prod/productshortname"></ph>
can transform the statement internally
 into one that contains only one of these keywords (the ORDER BY is thrown
 out). The requirements are:  



Mime
View raw message