Union

The UNION operator is much like a marriage between two people, even two companies: The union can be consummated, but that does not mean the parties are compatible. So it is with the UNION operator that can be used to combine the results of two or more queries into a single result set. The coupling results in the containment in the final result set of all the rows belonging to all the parties to the union.

While the UNION appears easy enough, it has a few sneaky rules that could scuttle the marriage:

• The number and the order of the columns in all source result sets must be identical.

• The data types in each corresponding column must be compatible. If they are different, it must be possible for SQL Server to implicitly convert them, and if not, then you will need to convert them using the CAST() or CONVERT() function before you can attempt the UNION. If you don’t, SQL Server will error out.

• If you combine columns that are assignment compatible, but of different types and thus implicitly converted by SQL Server, the resulting column is the data type of the column that was higher in the order of precedence.

• If you combine columns of fixed-length char, then the result of the UNION will produce a column that will be the length of the longer of the original columns.

• If you combine columns of fixed-length binary, the result of the UNION will produce a column that will be the length of the longer of the original columns.

• If you combine columns of variable-length char (varchar), then the result of the UNION will produce a column that will be the variable length of the longest character string of the original columns.

• If you combine columns of variable-length binary (varbinary), the result of the UNION will produce a column that will be the variable length of the longest binary string of the original columns.

• If you combine columns of different yet convertible data types, the result of the UNION will produce a column data type equal to the maximum precision of the two columns. In other words, if the source column of table A is of type integer and the source column of table B is of type float, then the data type of the UNION result will be float because float is more precise than integer.

• If the source columns specify NOT NULL, then the result of the UNION will also be NOT NULL.

• Unless explicitly provided, column names in the final result set are inherited from the first query.

The syntax for the UNION is as follows:

 { < query specification > ( < query expression > ) } UNION [ ALL ] < query specification | ( < query expression > ) [ UNION [ ALL ] < query specification | ( < query expression > ) [ ...n ] ]

UNION Arguments

According to Microsoft’s documentation, the placeholder < query_specification > | (<query_expression>) represents the query specification or query expression that returns the data to be combined with the data from another query specification or query expression. As mentioned earlier, the definitions of the columns that are part of a UNION do not have to be identical, but they must be compatible through implicit conversion.

The UNION argument is the keyword that specifies that multiple result sets are to be combined and returned as a single result set.

The ALL used as in UNION ALL incorporates all rows into the results, including duplicates. If it is not specified, duplicate rows are removed. If you can deal with duplicates and your result set is large, you may think about f oregoing ALL because it will f orce SQL Server to sort and evaluate the data in order to remove the duplicates. The UNION ALL thus costs a lot more than just UNION, as demonstrated in the two query plans.