Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

4.8 Unions

Unions are the last major type of query I'll examine
in this chapter. I find it helpful to think of unions as a way to
work with two queries stacked vertically atop each other. For
example, if you were interested in generating a list of all dates
used in the employee table, you could issue the
query in Example 4-39. The query consists of not one,
but two SELECT statements. The keyword UNION is the glue that joins
them together, producing one, combined column having all dates from
the two original columns.

Example 4-39. Stacking two queries vertically

SELECT employee_hire_date emp_date
FROM employee
UNION
SELECT employee_termination_date
FROM employee;
EMP_DATE
-----------
15-Nov-1961
16-Sep-1964
23-Aug-1976
...

UNION is an additive operation, so I tend to think of it in the
manner illustrated in Figure 4-9. Other so-called
union operations are not additive, but I still find the vertical
model helpful when writing union queries.

The UNION operation eliminates duplicates. No matter how many times a
given date appears in the employee table, the
query in Example 4-39 returns that date only one
time. Sometimes it's useful to
"see" all
occurrences. For example, you might wish to count the number of times
each date occurs. Example 4-40 does this, using a
UNION ALL query as a subquery that feeds a list of dates to an outer
query, a GROUP BY query that counts the number of times each distinct
date occurs.

Example 4-40. Preserving duplicates in a UNION operation

SELECT all_dates.emp_date, COUNT(*)
FROM (
SELECT employee_hire_date emp_date
FROM employee
UNION ALL
SELECT employee_termination_date
FROM employee) all_dates
GROUP BY all_dates.emp_date
ORDER BY COUNT(*) DESC;
EMP_DATE COUNT(*)
----------- ----------
5
15-Nov-1961 2
04-Apr-2004 2
16-Sep-1964 1
23-Aug-1976 1
...

The results in Example 4-40 show that five null dates
are in the employee table, two occurrences each of
15-Nov-1961 and 04-Apr-2004, and one occurrence each of the remaining
dates.

There are two other UNION operations, neither of which involves the
keyword UNION. You can use the INTERSECT operation to find values in
common between two result sets. Example 4-41 uses it
to find all dates on which both a hiring and a termination occurred.

Example 4-41. Finding rows in common between two result sets

SELECT employee_hire_date emp_date
FROM employee
INTERSECT
SELECT employee_termination_date
FROM employee;
EMP_DATE
-----------
04-Apr-2004 The last "union" operation is the
MINUS, which finds values in one result set that
aren't in another. Example 4-42 uses
MINUS to generate a list of employees who have never logged any time
against a project.

Example 4-42. Finding the difference between two result sets

SELECT employee_id
FROM employee
MINUS
SELECT employee_id
FROM project_hours;
EMPLOYEE_ID
-----------
116 Both INTERSECT and MINUS eliminate duplicate rows from their results.
INTERSECT returns at most one occurrence of any row in common between
two result sets. When you use MINUS, it takes only a single row in
the second result set to remove many occurrences of that same row
from the first result set.

Many problems that you might solve using union queries can also be
solved by other means. This doesn't mean that the
UNION operations don't have their place. Sometimes
they are a more efficient approach to solving a problem. Other times,
they are a more succinct and clear way of stating a query.