Share via


Combining Results Sets with UNION

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets that are combined by using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types. For more information, see Guidelines for Using Union.

UNION is specified as:

select_statement UNION [ALL] select_statement

For example, Table1 and Table2 have the same two-column structure.

TABLE1

 

 

TABLE2

 

COLUMNA

COLUMNB

 

COLUMNC

COLUMND

CHAR(4)

INT

 

CHAR(4)

INT

-------

---

 

-------

---

ABC

  1

 

GHI

  3

DEF

  2

 

JKL

  4

GHI

  3

 

MNO

  5

This query creates a UNION between the tables:

SELECT * FROM Table1
UNION
SELECT * FROM Table2

Here is the result set:

ColumnA  ColumnB
-------  --------
abc      1
def      2
ghi      3
jkl      4
mno      5

The result set column names of a UNION are the same as the column names in the result set of the first SELECT statement in the UNION. The result set column names of the other SELECT statements are ignored.

By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.

The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause. For more information about the effects of different collations, see Working with Collations.

Any number of UNION operators can appear in a Transact-SQL statement. For example:

SELECT * FROM TableA
UNION
SELECT * FROM TableB
UNION
SELECT * FROM TableC
UNION
SELECT * FROM TableD

By default, SQL Server 2005 evaluates a statement that contains UNION operators from left to right. Use parentheses to specify the order of evaluation. For example, the following statements are not equivalent:

/* First statement. */
SELECT * FROM TableA
UNION ALL
(   SELECT * FROM TableB
   UNION
   SELECT * FROM TableC
)
GO

/* Second statement. */
(SELECT * FROM TableA
 UNION ALL
 SELECT * FROM TableB
)
UNION
SELECT * FROM TableC)
GO

Remarks

In the first statement, duplicates are eliminated in the union between TableB and TableC. In the union between that set and TableA, duplicates are not eliminated. In the second statement, duplicates are included in the union between TableA and TableB, but are eliminated in the subsequent union with TableC. ALL has no effect on the final result of this expression.

When UNION is used, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. There can be only one ORDER BY or COMPUTE clause after the last SELECT statement; it is applied to the final, combined result set. GROUP BY and HAVING can be specified only in the individual SELECT statements.