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 Pervasive.SQL Engine orders the entire result set which, for large tables, can take several minutes. UNION ALL eliminates the need for the sort.

The Pervasive.SQL Engine does not support LONGVARBINARY columns in UNION statements. LONGVARCHAR is 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.

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

SELECT