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.
|
 | Mastering Oracle SQL |
Top
