UNION operator
The UNION
operator combines the results of two or more SELECT
statements into a single result. The single result is all of the returned records from the SELECT
statements. By default, duplicate records are not returned. To return duplicate records, use the ALL
keyword (UNION ALL
). The format is:
SELECT statement UNION [ALL] SELECT statement
When using the UNION
operator, the select lists for each SELECT
statement must have the same number of column expressions, with the same data types, and must be specified in the same order.
SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay, birth_date FROM person
The following example is not valid because the data types of the column expressions are different (SALARY
from EMP
has a different data type than LAST_NAME
from RAISES
). This example has the same number of column expressions in each SELECT
statement, but the expressions are not in the same order by data type.
SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM raises