UNION

UNION

You can use this operator to combine the results of two or more SELECT statements.

About the UNION SELECT command

This optional operator combines the results of two SELECT commands or clauses, displaying the results of the second SELECT command below those of the first. By default, UNION deletes duplicate rows. Include the optional keyword ALL to include duplicate rows in the final result. You cannot combine sub-SELECT commands using UNION.

The UNION operator requires the following three conditions:

The SELECT statements must specify an equal number of columns.

Columns that are being combined must have the same data type.

Only the last SELECT statement can contain an ORDER BY clause.

Examples

The following example lists all employees and the sales transactions for each, including those employees who have not yet completed a sale.

SELECT employee.empid, transid +
   FROM employee, transmaster +
   WHERE employee.empid = transmaster.empid +
UNION SELECT empid, 0 +
   FROM employee +
   WHERE employee.empid NOT IN +
      (SELECT empid FROM transmaster)

The first SELECT displays the empid column from the employee table and transid from the transmaster table, linking the tables by the common column, empid. In short, the first SELECT displays all employees who have made a sale.

The second SELECT command selects the empid column from employee, including rows only for those employees who are not listed in the transmaster table. Because the results of the second SELECT are appended to those of the first (by the UNION operator), those employees who have not yet made a sale are shown at the bottom of the results with a zero in the transid column. The final results look like this:

empid   transid
102   4795
102   4975
102   4980
102   5000
102   5045
102   5060
129   4790
129   4865
129   5050
129   5070
129   5075
131   4970
131   5010
131   5015
131   5085
133   4760
133   5048
133   5080
160   4780
160   4800
160   5065
165   5046
165   5049
166   0