Combine multiple results using the Union operator

Combine multiple results using the Union operator
The Union operator allows you to combine the results of two or more select statements into a result set. All result sets using the Union combination must have the same structure. And their columns

The numbers must be the same, and the Data Types of the corresponding result set columns must be compatible. For more information, see the Union operator user guide.

The method specified by Union is as follows:

Select_statement Union [all] select_statement

For example, Table1 and Table2 have the same structure of two columns.

Table1 Table2
Columna columnb columnc columnd
Char (4) int char (4) int
--------------------
ABC 1 Ghi 3
Def 2 jkl 4
Ghi 3 MnO 5

The following query creates a union operation between the two tables:

Select * From Table1
Union
Select * From Table2

The following is the result set:

Columna columnb
---------------
ABC 1
Def 2
Ghi 3
Jkl 4
MnO 5

The name of the Union result set column is the same as that of the first select statement in the Union operator. The names of the result set columns of another SELECT statement are ignored.

By default, the Union operator deletes duplicate rows from the result set. If the all keyword is used, all rows are included in the result and duplicate rows are not deleted.

The exact result of the union operation depends on the sorting rule selected during the installation process and the order by clause. For more information about the effects of different sorting rules, see SQL Server sorting

Basic knowledge of rules.

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, Microsoft & reg; SQL Server & #8482; 2000 takes values for statements containing the Union operator from left to right. Use parentheses to specify the order of values. For example, the following statement and

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

In the first statement, duplicate rows in the union between tableb and tablec are eliminated. Duplicate rows are not eliminated in the consortium between this set and tablea. In the second statement,

The union between tablea and tableb contains duplicate rows, but will be eliminated later in the Union with tablec. The All keyword does not affect the final result of this expression.

If the Union operator is used, a separate SELECT statement cannot contain its own order by or compute clause. It can only be used after the last SELECT statement

An order by or compute clause; this clause applies to the final combination result set. The group by and having clauses can only be specified in a separate SELECT statement.