db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "RPost" <rp0...@pacbell.net>
Subject Re: INTERSECT and EXCEPT Design
Date Sat, 26 Feb 2005 01:58:55 GMT
Intersect & Except Design>Jack Klebanoff wrote:

>The syntax is (roughly): 
>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?


> 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


> is equivalent to

>(t1 UNION ALL t2) UNION t3

Yes but it is not equivalent to 

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?

View raw message