The SQL command UNION appends a result set to another result set with similar structure. The basic syntax is as follows:

SelectStatement1
UNION [ALL]
SelectStatement2 [ORDER BY OrderList [ASC | DESC]]

The UNION command appends SelectStatement2 to the end of SelectStatement1. Both of the result sets must have the same structure. Each result set must have columns of compatible data types in corresponding order.

The ALL keyword can make the unioned result set returned duplicate values as well. The default, i.e. in the absence of the ALL keyword, duplicate rows are eliminated.

The optional ORDER BY clause can only be placed at the end of the last result set because it orders the entire unioned result set.

EGs

UNION

Union two simple tables:

tbl1 tbl2
col1 (int) col2 (char) col1 (int) col2 (char)
1 ab 7 gh
2 cd 8 ij
3 ef 9 kl 
SELECT * from tbl1
UNION
SELECT * from tbl2
Unioned Resultset
col1 (int) col2 (char)
1 ab
2 cd
3 ef
7 gh
8 ij
9 kl

Page Modified: (Hand noted: 2007-10-12 20:22:28Z) (Auto noted: 2007-11-17 06:45:20Z)