If we consider three single-column tables each having two rows: A = (1, 2), B = (2, 3), C = (3, 4). Then if we try UNION
and INTERSECT
together using parenthesis, the result is quite consistent:
(select * from a union select * from b) intersect select * from c
-> 3select * from a union (select * from b intersect select * from c)
-> 1, 2, 3But what about plain and simple...
select * from a union select * from b intersect select * from c
?I've tried it on several databases (SQL Fiddle) and what I empirically got is:
Do you guys know if there is any official definition on this? I skimmed the SQL-92 spec but couldn't find anything on the subject.
Oracle has this explanatory note in its documentation:
To comply with emerging SQL standards, a future release of Oracle will give the
INTERSECT
operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use theINTERSECT
operator with other set operators.
So, Oracle at least thinks that equal precedence is not consistent with the standard.
As a note: I often find the standard so inscrutable that hints like this are simpler than attempting to decipher the actual text.