Re: INTERSECT and EXCEPT Design

[RPost]

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