Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 21854 invoked from network); 26 Feb 2005 01:53:52 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 26 Feb 2005 01:53:52 -0000 Received: (qmail 94890 invoked by uid 500); 26 Feb 2005 01:53:51 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 94863 invoked by uid 500); 26 Feb 2005 01:53:51 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 94850 invoked by uid 99); 26 Feb 2005 01:53:51 -0000 X-ASF-Spam-Status: No, hits=2.3 required=10.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_POST,FROM_ENDS_IN_NUMS,HTML_30_40,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy includes SPF record at spf.trusted-forwarder.org) Received: from smtp812.mail.sc5.yahoo.com (HELO smtp812.mail.sc5.yahoo.com) (66.163.170.82) by apache.org (qpsmtpd/0.28) with SMTP; Fri, 25 Feb 2005 17:53:49 -0800 Received: from unknown (HELO rpws002) (rp0428@pacbell.net@68.125.10.130 with login) by smtp812.mail.sc5.yahoo.com with SMTP; 26 Feb 2005 01:53:46 -0000 Message-ID: <001801c51ba6$bc060780$1401a8c0@rpws002> From: "RPost" To: "Derby Development" References: <421FBEC8.5020803@sbcglobal.net> Subject: Re: INTERSECT and EXCEPT Design Date: Fri, 25 Feb 2005 17:58:55 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0015_01C51B63.AC465590" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C51B63.AC465590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Intersect & Except Design>Jack Klebanoff wrote: >The syntax is (roughly):=20 >queryExpression INTERSECT [ALL] queryExpression >queryExpression EXCEPT [ALL] queryExpression Although DISTINCT is implicit if ALL is not specified the SQL standard = also allows the DISTINCT operator to be specified explicitly. Would it = make sense to do that in Derby also? INTERSECT [ALL | DISTINCT] and EXCEPT [ALL | DISTINCT] > The architecture of the Derby optimizer makes it difficult to do = further optimizations. Yikes! Tread lightly there hoss, I think he (Jeffrey Lichtman) is = listening now. >The UNION and EXCEPT operators have the same precedence. The INTERSECT = operator has higher >precedence, so >t1 UNION ALL t2 UNION t3 > is equivalent to >(t1 UNION ALL t2) UNION t3 Yes but it is not equivalent to=20 t1 UNION ALL (t2 UNION t3) Anyway, I think you meant to compare UNION (or UNION ALL) and INTERSECT = here (as you did EXCEPT/INTERSECT below this) to illustrate the higher = precedence of INTERSECT. >IntersectOrExceptNode uses the ORDER BY columns as the most significant = part of the sort key for its inputs. Any >columns not in the ORDER BY = list are tacked on to the least significant part of the sort keys of the = inputs. This >ensures that the output of the INTERSECT or EXCEPT will be = properly ordered without an additional sort step You sly dog, you. Is any check made for primary keys or unique indexes on any of the = select columns for each of the tables? ------=_NextPart_000_0015_01C51B63.AC465590 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Intersect & Except Design
>Jack Klebanoff wrote:
 
>The syntax is (roughly):

>queryExpression INTERSECT [ALL]=20 queryExpression
>queryExpression EXCEPT [ALL]=20 queryExpression

Although DISTINCT is implicit if ALL is not specified the SQL = standard also=20 allows the DISTINCT operator to be specified explicitly. Would it = make=20 sense to do that in Derby also?

     INTERSECT [ALL | DISTINCT] and EXCEPT [ALL | = DISTINCT]

> The architecture of the Derby optimizer makes it = difficult to do=20 further optimizations.

Yikes! Tread lightly there hoss, I = think he=20 (Jeffrey Lichtman) is listening now.

>The UNION and EXCEPT operators have the same precedence. The = INTERSECT=20 operator has higher >precedence, so

>t1 UNION ALL t2 UNION t3

> is equivalent to

>(t1 UNION ALL t2) UNION t3

Yes but it is not equivalent to=20

t1 UNION ALL (t2 UNION t3)

Anyway, I think you meant to compare = UNION (or=20 UNION ALL) and INTERSECT here (as you did EXCEPT/INTERSECT below = this) to=20 illustrate the higher precedence of INTERSECT.

>IntersectOrExceptNode uses the ORDER BY columns as the most = significant=20 part of the sort key for its inputs. Any >columns not in the ORDER BY = list=20 are tacked on to the least significant part of the sort keys of the = inputs. This=20 >ensures that the output of the INTERSECT or EXCEPT will be properly = ordered=20 without an additional sort step

You sly dog, you.

Is any check made for primary keys or = unique indexes=20 on any of the select columns for each of the tables?

 

 

------=_NextPart_000_0015_01C51B63.AC465590--