Does INTERSECT have a higher precedence compared to UNION?

Question

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.

Parvis

Interesting. I would expect them to have the same precedence (similar to + and -). If there is a rule in the standard, I would assume that Postgres comes closest. That said, I would always use parentheses anyway. – Archducal

On Oracle SQL all set operations apparently have equal precedence. I don't have my ANSI SQL grammar handy (it's on another computer), so I can't speak for that right now. If you have it in front of you, you should be able to infer the precedence from it. – Stool

On what Postgres version did you try this? I get 1,2,3 in every version I tried, thus INTERSECT having higher precedence than UNION - and I think that agrees with the standard – Souter

From Postgres documentation (SELECT page): "Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C)." – Souter

Answer 1

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.

Archducal