>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?
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
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?
