Syntax

Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 89155 invoked from network); 2 Oct 2009 21:35:14 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 2 Oct 2009 21:35:14 -0000 Received: (qmail 95451 invoked by uid 500); 2 Oct 2009 21:35:14 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 95398 invoked by uid 500); 2 Oct 2009 21:35:14 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 95389 invoked by uid 99); 2 Oct 2009 21:35:13 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Oct 2009 21:35:13 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Oct 2009 21:35:10 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 084AE2388894; Fri, 2 Oct 2009 21:34:49 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r821180 - in /db/derby/docs/trunk/src/ref: refderby.ditamap rrefkeywords29722.dita rrefsqlj29840.dita rrefsqljcrossjoin.dita Date: Fri, 02 Oct 2009 21:34:48 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20091002213449.084AE2388894@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: chaase3 Date: Fri Oct 2 21:34:48 2009 New Revision: 821180 URL: http://svn.apache.org/viewvc?rev=821180&view=rev Log: DERBY-4374: Document the CROSS JOIN operation in the reference manual Added new topic, edited two others. Patch: DERBY-4374-3.diff Added: db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita (with props) Modified: db/derby/docs/trunk/src/ref/refderby.ditamap db/derby/docs/trunk/src/ref/rrefkeywords29722.dita db/derby/docs/trunk/src/ref/rrefsqlj29840.dita Modified: db/derby/docs/trunk/src/ref/refderby.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/refderby.ditamap?rev=821180&r1=821179&r2=821180&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/refderby.ditamap (original) +++ db/derby/docs/trunk/src/ref/refderby.ditamap Fri Oct 2 21:34:48 2009 @@ -249,6 +249,8 @@ + + Modified: db/derby/docs/trunk/src/ref/rrefkeywords29722.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefkeywords29722.dita?rev=821180&r1=821179&r2=821180&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefkeywords29722.dita (original) +++ db/derby/docs/trunk/src/ref/rrefkeywords29722.dita Fri Oct 2 21:34:48 2009 @@ -72,6 +72,7 @@ CONVERT CORRESPONDING CREATE +CROSS CURRENT CURRENT_DATE CURRENT_ROLE Modified: db/derby/docs/trunk/src/ref/rrefsqlj29840.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj29840.dita?rev=821180&r1=821179&r2=821180&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqlj29840.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqlj29840.dita Fri Oct 2 21:34:48 2009 @@ -30,16 +30,19 @@ tables using an explicit equality test in a WHERE clause, such as "WHERE t1.col1 = t2.col2".)

Syntax JOIN Operation

The -JOIN operations are:

Specifies a -join between two tables with an explicit join clause. See .

Specifies a join between two tables with an explicit join clause, preserving unmatched -rows from the first table. See .

Specifies a join between two tables with an explicit join clause, preserving unmatched -rows from the second table. See .

In all cases, you can specify additional restrictions on one +rows from the second table.

Specifies a +join that produces the Cartesian product of two tables. It has no explicit join +clause.

In all cases, you can specify additional restrictions on one or both of the tables being joined in outer join clauses or in the WHERE clause.

JOIN expressions and query optimization

For information Added: db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita?rev=821180&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita Fri Oct 2 21:34:48 2009 @@ -0,0 +1,64 @@ + + + + + +CROSS JOIN operation + +CROSS JOIN operation + + +

A CROSS JOIN is a +JOIN operation that +produces the Cartesian product of two tables. Unlike other JOIN operators, it +does not let you specify a join clause. You may, however, specify a WHERE clause +in the SELECT statement.

+Syntax +TableExpression CROSS JOIN { TableViewOrFunctionExpression | ( TableExpression ) } + +Examples +

The following SELECT statements are equivalent:

+SELECT * FROM CITIES CROSS JOIN FLIGHTS +SELECT * FROM CITIES, FLIGHTS +

The following SELECT statements are equivalent:

+SELECT * FROM CITIES CROSS JOIN FLIGHTS + WHERE CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT +SELECT * FROM CITIES INNER JOIN FLIGHTS + ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT +

The following example is more complex. The ON clause in this example is +associated with the LEFT OUTER JOIN operation. Note that you can use parentheses +around a JOIN operation.

+SELECT * FROM CITIES LEFT OUTER JOIN + (FLIGHTS CROSS JOIN COUNTRIES) + ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT + WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US' +

A CROSS JOIN operation can be replaced with an INNER JOIN where the join +clause always evaluates to true (for example, 1=1). It can also be replaced with +a sub-query. So equivalent queries would be:

+SELECT * FROM CITIES LEFT OUTER JOIN + FLIGHTS INNER JOIN COUNTRIES ON 1=1 + ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT + WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US' +SELECT * FROM CITIES LEFT OUTER JOIN + (SELECT * FROM FLIGHTS, COUNTRIES) S + ON CITIES.AIRPORT = S.ORIG_AIRPORT + WHERE S.COUNTRY_ISO_CODE = 'US' + + + Propchange: db/derby/docs/trunk/src/ref/rrefsqljcrossjoin.dita ------------------------------------------------------------------------------ svn:eol-style = native