SAND CDBMS SQL Reference Guide
SELECT

The UNION Operator

The UNION operator can be included between two or more SELECT statements to combine the multiple result tables into a single result table. The tables in the union must have the same number of columns/expressions in their respective SELECT lists, and the data types must be compatible (consult the section Data Type Compatibility). A maximum of 33 SELECT statements can be combined in this manner (that is, the UNION keyword can appear a maximum of 32 times in the same SQL statement.

Functions, as well as GROUP BY and HAVING clauses, can be used in the SELECT statements. Only the last SELECT statement in the UNION may have a WITH SAMPLE OF clause, which returns a subset of the UNION output table. As well, only the last SELECT statement in the UNION may have an ORDER BY clause, which is used to order the UNION output table. The ORDER BY clause must reference the output columns by number.

Note that the optional SELECT clauses, if included, must appear in the following order:

1. WHERE
2. GROUP BY
3. HAVING
4. WITH SAMPLE OF (last SELECT statement only)
5. ORDER BY (last SELECT statement only)
6. FETCH FIRST...ONLY (last SELECT statement only)

When a projected column or correlation name is the same for all SELECT statements in the UNION, that column/correlation name is used as the column header in the output table. If there are conflicting column names, or if expressions are used, the column header in the output table will be the number of the column, counting from left to right.

Apart from nested table expressions, the UNION operator may not be used in subqueries.

UNION vs. UNION ALL

Specify UNION between two SELECT statements to combine both result sets into one table. By default, duplicate rows are omitted from the result set produced by the UNION operator. To return duplicate columns in the result set, specify UNION ALL.

Example

Consider the following tables:

e_no lname fname 1281 Harris Joe 1426 Jacobi Ron 1498 Mann Susan 1743 Prince Peter employee_usa table

e_no lname fname 0987 Desai Anita 1176 Gilbert Gilles 1509 McCormack Fred 1998 Twain James employee_can table

Employing the UNION operator between a SELECT statement from both tables, for example,

SELECT * FROM employee_usa
UNION
SELECT * FROM employee_can;

will give the following result set:

e_no	lname	fname
1281	Harris	Joe
1426	Jacobi	Ron
1498	Mann	Susan
1743	Prince	Peter
0987	Desai	Anita
1176	Gilbert	Gilles
1509	McCormack	Fred
1998	Twain	James

UNION of employee_usa and employee_can tables