7.1 Set Operators
The following list briefly describes the four set operations
supported by Oracle SQL:
- UNION ALL
-
Combines
the results of two SELECT statements
into one result set.
- UNION
-
Combines
the results of two SELECT statements
into one result set, and then eliminates any duplicate rows from that
result set.
- MINUS
-
Takes the
result set of one SELECT statement,
and removes those rows that are also returned by a second SELECT
statement.
- INTERSECT
-
Returns
only those rows that are returned by
each of two SELECT statements.
Before moving on to the details on these set operators,
let's look at the following two queries, which
we'll use as component queries in our subsequent
examples. The first query retrieves all the customers in region 5.
SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5;
CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
The second query retrieves all the customers with the sales
representative is 'MARTIN'.
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');
CUST_NBR NAME
---------- ------------------------------
4 Flowtech Inc.
8 Zantech Inc.
If we look at the results returned by these two queries, we will
notice that there is one common row (for Flowtech Inc.). The
following sections discuss the effects of the various set operations
between these two result sets.
7.1.1 UNION ALL
The UNION ALL
operator merges the result sets of
two component queries. This operation returns rows retrieved by
either of the component queries. The following example illustrates
the UNION ALL operation:
SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
UNION ALL
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');
CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
4 Flowtech Inc.
8 Zantech Inc.
7 rows selected.
As we can see from the result set, there is one customer, which is
retrieved by both the SELECTs, and therefore appears twice in the
result set. The UNION ALL operator simply merges the output of its
component queries, without caring about any duplicates in the final
result set.
7.1.2 UNION
The UNION
operator returns all distinct rows
retrieved by two component queries. The UNION operation eliminates
duplicates while merging rows retrieved by either of the component
queries. The following example illustrates the UNION operation:
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');
CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
8 Zantech Inc.
6 rows selected.
This query is a modification of the previous query; the keywords
UNION ALL have been replaced with UNION. Notice that the result set
contains only distinct rows (no duplicates). To eliminate duplicate
rows, a UNION operation needs to do some extra tasks as compared to
the UNION ALL operation. These extra tasks include sorting and
filtering the result set. If we observe carefully, we will notice
that the result set of the UNION ALL operation is not sorted, whereas
the result set of the UNION operation is sorted. These extra tasks
introduce a performance overhead to the UNION operation. A query
involving UNION will take extra time compared to the same query with
UNION ALL, even if there are no duplicates to remove. Therefore,
unless we have a valid need to retrieve only distinct rows, we should
use UNION ALL instead of UNION for better performance.
7.1.3 INTERSECT
INTERSECT
returns only the rows retrieved by
both component queries. Compare this with UNION, which returns the
rows retrieved by any of the component queries. If UNION acts like
'OR', INTERSECT acts like 'AND'. For example:
SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
INTERSECT
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');
CUST_NBR NAME
---------- ------------------------------
4 Flowtech Inc.
As we saw earlier, "Flowtech Inc."
was the only customer retrieved by both SELECT statements. Therefore,
the INTERSECT operator returns just that one row.
7.1.4 MINUS
MINUS returns
all rows from the first SELECT that
are not also returned by the second SELECT. For example:
SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
MINUS
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');
CUST_NBR NAME
---------- ------------------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
5 Gentech Industries
You might wonder why we don't see
"Zantech Inc." in the output. An
important thing to note here is that the execution order of component
queries in a set operation is from top to bottom. The results of
UNION, UNION ALL, and INTERSECT will not change if we alter the
ordering of component queries. However, the result of MINUS will be
different if we alter the order of the component queries. If we
rewrite the previous query by switching the positions of the two
SELECTs, we get a completely different result:
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')
MINUS
SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5;
CUST_NBR NAME
---------- ------------------------------
8 Zantech Inc.
The row for "Flowtech Inc." is
returned by both queries, so in our first MINUS example the first
component query adds "Flowtech
Inc." to the result set while the second component
query removes it. The second example turns the MINUS operation
around. The first component query adds "Flowtech
Inc." and "Zantech
Inc." to the result set. The second component query
specifies rows to subtract. One of the rows to subtract is
"Flowtech Inc.", leaving
"Zantech Inc." as the sole
remaining row.
 |
In a MINUS operation, rows may be returned by the second SELECT that
are not also returned by the first. These rows are not included in
the output.
|
|
|
