MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual, Page: 464 / 860

464 Chapter 22: Using Query of Queries
The UNION statement produces the following result (UNION) table:
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
)
Result table
Type(int) Name(varchar)
1 Tennis
2 Baseball
3 Football
4 Volleyball
5 PingPong