As documented, multiple UNIONs are is documented from left to right:

By default, a statement containing multiple UNION operators is evaluated from left to right. Parentheses may be used to specify the order of evaluation.

What's the default precedence when combining multiple UNION and EXCEPT clauses?

With SA 12.0.1.3298, UNION and EXCEPT (and INTERSECT?) seem to have the same precedence, and seem all to be computed from left to right. I.e.

:::SQL
select 1 from dummy
except
select 2 from dummy
union
select 2 from dummy

returns the same as the - obviously not valid ! - statement

:::SQL
(select 1 from dummy
except
select 2 from dummy)
union
select 2 from dummy

namely the rows "1" and "2", whereas

:::SQL
select 1 from dummy
except
(select 2 from dummy
union
select 2 from dummy)

just returns the row "1".

Now what I would like to get is the union of two excepts - as in the very simplified example:

:::SQL
(select 1 from dummy
except
select 2 from dummy)
union
(select 2 from dummy
except
select 1 from dummy)

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?

asked 08 Apr '11, 13:48

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 08 Apr '11, 13:49


The grammar rules used in SQL Anywhere include the following:

%left T_UNION T_EXCEPT
%left T_INTERSECT
%right T_CONCAT
%left '+' '-' 
%left '*' '/' '%'
%left '&' '|' '^' '~'
%left '.' '('

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) :-)

permanent link

answered 13 Apr '11, 11:16

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

I'm particularly grateful for the "human speak" part:) - Thanks for confirming Tyson's statement. FWIW, I've left a note in DCX yesterday to add that information.

(13 Apr '11, 11:33) Volker Barth

I believe UNION and EXCEPT have equal precedence. INTERSECT has higher precedence. All three are evaluated left to right.

permanent link

answered 11 Apr '11, 14:51

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

Is there any official statement on these operations to confirm Tyson's statement (which fit my tests)?

(12 Apr '11, 16:05) Volker Barth

Volker,

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.

i.e.

:::SQL
SELECT * FROM (

(SELECT * FROM systab WHERE table_id = 1
EXCEPT
SELECT * FROM systab WHERE table_id = 2)
UNION
(SELECT * FROM systab WHERE table_id = 2
EXCEPT
SELECT * FROM systab WHERE table_id = 1)

) AS dt;

The above is a extremely simplistic example, just be sure that all columns have names.

permanent link

answered 08 Apr '11, 20:30

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

Thanks for the pointer - I'm gonna test that with DBISQL...

I wasn't aware that these tools do their own syntax checks - as such I hadn't thought of dbisqlc limitations because it does allow UNION and EXCEPT in general.

(09 Apr '11, 07:02) Volker Barth

Tyson, I've confirmed that the enclosed statements do work correctly with DBISQL.

However, my title question is still unanswered...

(11 Apr '11, 03:20) Volker Barth
2

This problem with select statements that start with an open parenthesis is fixed in dbisqlc 11.0.1.2591, 12.0.0.2670 and 12.0.1.3329.

Dbisqlc does minimal parsing of most statements but it must do some to identify its own statements (e.g. INPUT). For statements not handled by dbisqlc itself, the first token also tells us whether to execute the statement or to open a cursor. Although other mechanisms exist for that today, they didn't when dbisqlc was first written.

(14 Apr '11, 15:13) John Smirnios
Replies hidden
1

Well, John, your constant ability and readiness to fix dbisqlc issues won't help me to leave that deprecated tool behind ... thanks again:)

(15 Apr '11, 03:28) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×10
×5

question asked: 08 Apr '11, 13:48

question was seen: 8,318 times

last updated: 15 Apr '11, 03:28