As documented, multiple UNIONs are is documented from left to right:
What's the default precedence when combining multiple UNION and EXCEPT clauses?
With SA 220.127.116.1198, UNION and EXCEPT (and INTERSECT?) seem to have the same precedence, and seem all to be computed from left to right. I.e.
returns the same as the - obviously not valid ! - statement
namely the rows "1" and "2", whereas
just returns the row "1".
Now what I would like to get is the union of two excepts - as in the very simplified example:
Unfortunately, I seem to need parantheses to enforce this precedence but can't find the correct syntax - dbisqlc just issues a syntax error for this query.
What am I doing wrong?
The grammar rules used in SQL Anywhere include the following:
The above (in human-speak) means that UNION and EXCEPT have equal precedence but will bind from left to right. INTERSECT has a higher precedence than UNION and EXCEPT and also binds left to right when more than one INTERSECT is used. The rest is left as an exercise for the reader (and hopefully should not be too hard to figure out) :-)
answered 13 Apr '11, 11:16
I believe UNION and EXCEPT have equal precedence. INTERSECT has higher precedence. All three are evaluated left to right.
answered 11 Apr '11, 14:51
You're last query seems to be correct. If you run it in DBISQL, no Syntax Error is reported. This seems to be a limitation with DBISQLC, and is not likely to be changed as DBISQLC is deprecated.
If you have the java ISQL available, you should be able to run the query with that. Alternatively, you could wrap your actual union into a derived table and select from that.
The above is a extremely simplistic example, just be sure that all columns have names.
answered 08 Apr '11, 20:30