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

Taking Advantage of Unions

As I described in Chapter 4, a union is an SQL construct that allows you to knit together the results of several SQL queries and treat those results as if they had been returned by just one query. I find them invaluable when writing queries. One of the more creative uses I've discovered involves using unions to produce reports that need to show data grouped by categories and that may need to show the same records in more than one of those categories.

A Typical Union Example

A good example of this type of report would be one that fulfills the following request:

Produce an employee turnover report that lists everyone employed at the beginning of the year, everyone hired during the year, everyone terminated during the year, and everyone employed at the end of the year. The report should be divided into four sections, one for each of those categories.

This is a common kind of request, for me at least. The interesting thing about this request, though, is that every employee will need to be listed in two categories. That means you would need to write a query that returned each employee record twice in the correct categories.

When you are faced with this type of query, it can be helpful to simplify the problem by thinking in terms of separate queries, one for each category. It's fairly easy to conceive of a query to bring back a list of employees that were on board at the beginning of the year. You need to make sure the first of the year is between the hire date and ...