Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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.

Figure 4-9. Using UNION to combine rows from two SELECT statements into one result set

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 ...