• Login

DataSoft Corporation

UNION

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

The Pervasive PSQL database 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.

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
Thursday, 18 April 2019 Posted in Pervasive SQL Command
 DataSoft Store  Knowledgebase  Contact Us
 Sitemap  Privacy Policy
    Customer Service


Copyright © 2019 DataSoft Corporation All rights reserved.