UNION Clause
The UNION clause combines the results of SELECT statements into a single result table.
The following example lists all employees in the table of active employees plus those in the table of retired employees:
SELECT ename FROM active_emps
UNION
SELECT ename FROM retired_emps;
By default, the UNION clause eliminates any duplicate rows in the result table. To retain duplicates, specify UNION ALL. Any number of SELECT statements can be combined using the UNION clause, and both UNION and UNION ALL can be used when combining multiple tables.
If you know that the result sets you want to combine from the different SELECT statements are unique, or if uniqueness is not a concern, then use UNION ALL to get better performance.
Unions are subject to the following restrictions:
• The SELECT statements must return the same number of columns.
• The columns returned by the SELECT statements must correspond in order and data type, although the column names do not have to be identical.
• The SELECT statements cannot include individual ORDER BY clauses.
To sort the result table, specify the ORDER BY clause following the last SELECT statement. The result columns returned by a union are named according to the first SELECT statement.
By default, unions are evaluated from left to right. To specify a different order of evaluation, use parentheses.
Any number of SELECT statements can be combined using the UNION clause. A maximum of 126 tables is allowed in a query.