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