Using unions

The UNION operator lets you combine the results of two or more SELECT expressions into a single record set. The original tables must have the same number of columns, and corresponding columns must be UNION-compatible data types. Columns are UNION-compatible data types if they meet one of the following conditions:

The same data type; for example, both Tinyint
Both Numeric; for example, Tinyint, Smallint, Integer, Bigint, Double, Float, Real, Decimal, or Numeric
Both Characters; for example, Char, Varchar, or LongVarchar
Both Dates; for example, Time, TimeStamp, or Date

Note: Query Of Queries does not support ODBC-formatted dates and times.

Syntax

select_expression = select_expression UNION [ALL] select_expression

Example

This example uses the following tables:

Table1
Type(int)	Name(varchar)
1	Tennis
2	Baseball
3	Football

Table2
ID(int)	Sport(varchar)
3	Football
4	Volleyball
5	PingPong

To combine Table1 and Table2, use a UNION statement, as follows:

SELECT * FROM Table1 
UNION 
SELECT * FROM Table2

The UNION statement produces the following result (UNION) table:

Result table
Type(int)	Name(varchar)
1	Tennis
2	Baseball
3	Football
4	Volleyball
5	PingPong

Using aliases for column names

The column names of a UNION table are the column names in the result set of the first SELECT statement in the UNION operation; ColdFusion ignores the column names in the other SELECT statement. To change the column names of the result table, you can use an alias, as follows:

Select Type as SportType, Name as SportName from Table1
UNION
Select * from Table2

Duplicate rows and multiple tables

By default, the UNION operator removes duplicate rows from the result table. If you use the keyword ALL, then duplicates are included.

You can combine an unlimited number of tables using the UNION operator, for example:

Select * from Table1
UNION 
Select * from Table2
UNION 
Select * from Table3
...

Parentheses and evaluation order

By default, the Query of Queries SQL engine evaluates a statement containing UNION operators from left to right. You can use parentheses to change the order of evaluation. For example, the following two statements are different:

/* First statement. */ 
SELECT * FROM TableA
UNION ALL 
(SELECT * FROM TableB 
UNION 
SELECT * FROM TableC
) 

/* Second statement. */ 
(SELECT * FROM TableA 
UNION ALL 
SELECT * FROM TableB 
) 
UNION 
SELECT * FROM TableC

In the first statement, there are no duplicates in the union between TableB and TableC. Then, in the union between that set and TableA, the ALL keyword includes the duplicates. In the second statement, duplicates are included in the union between TableA and TableB but are eliminated in the subsequent union with TableC. The ALL keyword has no effect on the final result of this expression.

Using other keywords with UNION

When you perform a UNION, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. You can only have one ORDER BY or COMPUTE clause after the last SELECT statement; this clause is applied to the final, combined result set. You can only specify GROUP BY and HAVING expressions in the individual SELECT statements.

View comments in LiveDocs