sqlunionintersect

Does INTERSECT have a higher precedence compared to UNION?


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:

But what about plain and simple...

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.


Solution

  • 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 the INTERSECT 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.