Search code examples
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:

  • (select * from a union select * from b) intersect select * from c -> 3
  • select * from a union (select * from b intersect select * from c) -> 1, 2, 3

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

  • In one corner we have Oracle and H2 that consider INTERSECT having the same precedence as UNION (hence the result is 3).
  • Then, in the other corner is DB2, PostgreSQL, SQL Server, MariaDB, Apache Derby, and HyperSQL that consider INTERSECT having a higher precedence than UNION (hence the result is 1, 2, 3).
  • MySQL and Sybase ASE stay out of the ring, since they don't implement INTERSECT at all.

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.