| If more than two component queries are combined using set operators, then Oracle evaluates the set operators from left to right. In the following example, the UNION is evaluated before the INTERSECT: SELECT cust_nbr, name FROM customer WHERE region_id = 5 UNION SELECT c.cust_nbr, c.name FROM customer c WHERE c.cust_nbr IN (SELECT o.cust_nbr FROM cust_order o, employee e WHERE o.sales_emp_id = e.emp_id AND e.lname = 'MARTIN') INTERSECT SELECT cust_nbr, name FROM customer WHERE region_id = 6; CUST_NBR NAME -------- ------------------------------ 8 Zantech Inc.
To influence a particular order of evaluation of the set operators, you can use parentheses. Looking at the preceding example, if you want the INTERSECT to be evaluated before the UNION, you should introduce parentheses into the query such that the component queries involving the INTERSECT are enclosed in parentheses, as shown in the following example: SELECT cust_nbr, name FROM customer WHERE region_id = 5 UNION ( SELECT c.cust_nbr, c.name FROM customer c WHERE c.cust_nbr IN (SELECT o.cust_nbr FROM cust_order o, employee e WHERE o.sales_emp_id = e.emp_id AND e.lname = 'MARTIN') INTERSECT SELECT cust_nbr, name FROM customer WHERE region_id = 6 ); CUST_NBR NAME -------- ------------------------------ 1 Cooper Industries 2 Emblazon Corp. 3 Ditech Corp. 4 Flowtech Inc. 5 Gentech Industries 8 Zantech Inc.
The operation within the parentheses is evaluated first. The result is then combined with the component queries outside the parentheses.  | The ANSI/ISO SQL standard gives higher precedence to the INTERSECT operator. However, Oracle, at least through Oracle Database 10g, doesn't implement that higher precedence. All set operations currently have equal precedence. In the future, Oracle may change the precedence of INTERSECT to comply with the standard. To prepare for that possibility, we recommend using parentheses to control the order of evaluation of set operators whenever you use INTERSECT in a query with any other set operator. |
|
|
