UNION
Remarks
SELECT statements that use UNION or UNION ALL allow you to obtain a single result table from multiple SELECT queries. UNION queries are suitable for combining similar information contained in more than one data source.
UNION eliminates duplicate rows. UNION ALL preserves duplicate rows. Using the UNION ALL option is recommended unless you require duplicate rows to be removed.
With UNION, the PSQL Engine orders the entire result set which, for large tables, can take several minutes. UNION ALL eliminates the need for the sort.
The PSQL Database Engine does not support LONGVARBINARY columns in UNION statements. LONGVARCHAR and NLONGVARCHAR are limited to 65500 bytes in UNION statements. The operator UNION cannot be applied to any SQL statement that references one or more views.
The two query specifications involved in a union must be compatible. Each query must have the same number of columns and the columns must be of compatible data types.
You may use column names from the first SELECT list in the ORDER BY clause of the SELECT statement that follows the UNION keyword. Ordinal numbers are also allowed to indicate the desired columns. For example, the following statements are valid:
SELECT c1, c2, c3 FROM t1 UNION SELECT c4, c5, c6 FROM t2 ORDER BY t1.c1, t1.c2, t1.c3
SELECT c1, c2, c3 FROM t1 UNION SELECT c4, c5, c6 FROM t2 ORDER BY 1, 2, 3
You may also use aliases for the column names:
SELECT c1 x, c2 y, c3 z FROM t1 UNION SELECT c1, c2, c3 FROM t2 ORDER BY x, y, z
SELECT c1 x, c2 y, c3 z FROM t1 a UNION SELECT c1, c2, c3 FROM t1 b ORDER BY a.x, a.y, a.z
Aliases must differ from any table names and column names in the query.
Examples
The following example lists the ID numbers of each student whose last name begins with 'M' or who has a 4.0 grade point average. The result table does not include duplicate rows.
SELECT Person.ID from Person WHERE Last_name LIKE M%' UNION SELECT Student.ID FROM Student WHERE Cumulative_GPA = 4.0
The next example lists the column id in the person table and the faculty table including duplicate rows.
SELECT person.id FROM person UNION all SELECT faculty.id from faculty
The next example lists the ID numbers of each student whose last name begins with 'M' or who has a 4.0 grade point average. The result table does not include duplicate rows and orders the result set by the first column
SELECT Person.ID FROM Person WHERE Last_name LIKE M%' UNION SELECT Student.ID FROM Student WHERE Cumulative_GPA = 4.0 order by 1
It is common to use the NULL scalar function to allow a UNION select list to have a different number of entries than the parent select list. To do this, you must use the CONVERT function to force the NULL to the correct type.
CREATE TABLE t1 (c1 INTEGER, c2 INTEGER)
INSERT INTO t1 VALUES (1,1)
CREATE TABLE t2 (c1 INTEGER)
INSERT INTO t2 VALUES (2)
select c1, c2 from t1
UNION SELECT c1, convert(null(),sql_integer)FROM t2
See Also