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


SELECT employee_termination_date

FROM employee;







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(*)


 SELECT employee_hire_date emp_date

 FROM employee


 SELECT employee_termination_date

 FROM employee) all_dates

GROUP BY all_dates.emp_date



----------- ----------


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


SELECT employee_termination_date

FROM employee;




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


SELECT employee_id

FROM project_hours;




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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

show all menu

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net