The UNION, EXCEPT, and INTERSECT set operators enable you to combine two or more outer-level queries into a single query. Each of the queries connected by these set operators is executed and the individual results are combined. Depending on the operator, a different result is produced.
The UNION operator derives a result table by combining two other result tables (for example TABLE1 and TABLE2) and eliminating any duplicate rows in the tables. When ALL is used with UNION (that is, UNION ALL), duplicate rows are not eliminated. In either case, each row of the derived table is a row from either TABLE1 or TABLE2.
In the following example of the UNION operator, the query returns the names of all persons that have a salary greater than $21, 000, or that have managerial responsibilities and have been working for less than 8 years:
(1)
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
UNION
(2)
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
ORDER BY ID
The result of the individual queries are as follows:
(1)
ID NAME
------ ---------
140 Fraye
160 Molinare
260 Jones
(2)
ID NAME
------ ---------
10 Sanders
30 Marenghi
100 Plotz
140 Fraye
160 Molinare
240 Daniels
The database manager combines the results of both queries, eliminates the duplicates, and returns the final result in ascending order.
ID NAME
------ ---------
10 Sanders
30 Marenghi
100 Plotz
140 Fraye
160 Molinare
240 Daniels
260 Jones
If you use the ORDER BY clause in a query with any set operator, you must write it after the last query. The system applies the ordering to the combined answer set. If the column name in the two tables is different, the combined result table does not have names for the corresponding columns. Instead, the columns are numbered in the order in which they appear. So, if you want the result table to be ordered, you have to specify the column number in the ORDER BY clause.
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and eliminating all duplicate rows. When you use ALL with EXCEPT (EXCEPT ALL), the duplicate rows are not eliminated.
In the following example of the EXCEPT operator, the query returns the names of all persons that earn over $21, 000 but do not have the position of a manager and have been there 8 years or more.
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
EXCEPT
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
The result of the individual queries is listed in the section on UNION. The above statement produces the following result:
ID NAME
------ ---------
260 Jones
The INTERSECT operator derives a result table by including only rows that exist in both TABLE1 and TABLE2 and eliminating all duplicate rows. When you use ALL with INTERSECT (INTERSECT ALL), the duplicate rows are not eliminated.
In the following example of the INTERSECT operator, the query returns the name and ID of employees that earn more than $21, 000, have managerial responsibilites and have been working for fewer than 8 years.
SELECT ID, NAME FROM STAFF WHERE SALARY > 21000
INTERSECT
SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8
The result of the individual queries is listed in the section on UNION. The outcome of the two queries with INTERSECT is:
ID NAME
------ ---------
140 Fraye
160 Molinare
When using the UNION, EXCEPT, and INTERSECT operators, keep the following in mind: