UNION (SQL Server Compact)

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.

Syntax

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

Arguments

  • < 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.

Example

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