You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by jt...@apache.org on 2005/06/08 17:29:13 UTC
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
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: