Menu
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.
426
You can combine multiple queries using the set operators UNION , UNION ALL , INTERSECT , and MINUS . All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.
UNION combines results from both tables. UNION ALL combines two or more result sets into a single set, including all duplicate rows. INTERSECT takes the rows from both the result sets which are common in both. EXCEPT takes the rows from the first result data but does not in the second result set.
All of the set operators have the same precedence. In complex queries that include more than one set operator, the precedence of operators is from left to right. Use parentheses to group set operators and their operands, if you need to override the default left-to-right precedence of set operators.
INTERSECT gives you the rows that are found in both queries by eliminating rows that are only found in one or the other query. MINUS gives you the rows that are found in the first query and not in the second query by removing from the results all the rows that are found only in the second query.
Oracle has this explanatory note in its documentation:
To comply with emerging SQL standards, a future release of Oracle will give the
INTERSECToperator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use theINTERSECToperator 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.
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
