Export (0) Print
Expand All

UNION

SQL Server 2000

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 > )
Is 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.
Examples

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

Show:
© 2014 Microsoft