>Edward Rayl wrote:
>I have not used EXCEPT, but I am assuming, as well, that it maps to MINUS.

Oracle's MINUS is the equivalent of EXCEPT and returns unique (distinct) rows.
Oracle uses the same precedence for UNION, INTERSECT and MINUS. So what precedence should Derby use?
I agree with previous comments that a strategy should be discussed and adopted. A starting point might be to see where folks stand on the relative importance of various aspects of the issue:
1. Rank the importance of being compatible with each of the other databases of concern.
    A. Which DB is it most important to be compatible with? My vote here is DB2 since Derby would not exist as opensource without IBM's generosity. Since there is every indication that they intend to provide continued support and cooperation to the project it makes sense to me to make that as attractive as possible unless there is a compelling reason to diverge.
2. How important (on a scale of 1-10) is portability in migrating FROM Derby to other databases?
    A. Derby's use of UNION DISTINCT would be non-portable to ORACLE and DB2 but the non-portability would not go unnoticed; it would be simple to find and fix the problem.
    B. Derby's use of higher precedence for INTERSECT would be non-portable to ORACLE since oracle uses the same precedence. This could easily go unnoticed since there would be no compile error but a difference in the result set. This type of non-visible portability issue can be very hard to detect.
3. How important is portability in migrating TO Derby from other databases?
    A. Derby's use of UNION DISTINCT would be portable since the word DISTINCT is not mandatory.
4. Should Derby adopt the Least Common Denominator standard?
My own experience is that, in practice, portability is over-hyped. Every Oracle-to-DB2 (and vice versa) project I have worked on involved examining every important query manually to optimize it. I would much rather have a query not work, perhaps because of the use of UNION DISTINCT rather than just UNION, than work but give different results.
Oracle allows embedded hints making such queries non-portable to almost every other database. For years DB2 supported TABLE expressions while other database did not; all of those queries were non-portable.
Unless we plan to maintain a table like that in 'http://www.dbazine.com/gulutzan3.shtml' of differences the closer we stick to the SQL standard the easier it will be for integrator's to know how to work with Derby in their environment.