Export (0) Print
Expand All
Expand Minimize

UNION (SQL Server Compact Edition)

Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union.


{ < query_specification > | ( < query_expression > ) } 
     UNION [ ALL ] 
     < query_specification | ( < query_expression > ) 
     [ UNION [ ALL ] < query_specification | ( < query_expression > ) 
         [ ...n ] ] 

< query_specification > | ( < query_expression > )

A query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation do not have to be identical, but they must be compatible through implicit conversion.

UNION

Specifies that multiple result sets are combined and returned as a single result set.

ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

The following example returns the union of two SELECT statements.

CREATE TABLE t1 (a int, b nchar(4), c nchar(4))
INSERT INTO t1 VALUES (1, 'abc', 'jkl')
INSERT INTO t1 VALUES (2, 'def', 'mno')
INSERT INTO t1 VALUES (3, 'ghi', 'pqr')

CREATE TABLE t2 (a nchar(4), b float)
INSERT INTO t2 VALUES('jkl', 1.000)
INSERT INTO t2 VALUES('mno', 3.000)

SELECT a, b FROM t1
UNION
SELECT b, a FROM t2

This is the result set:

a ..........b
-------------
1...........abc
1...........jkl
2...........def
3...........ghi
3...........mno
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft