Monday, April 21, 2014

TSQL Tutorial: UNION Operator

Description

The TSQL UNION operator is used to combine the result sets of 2 or more SELECT statements.
It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION must have the same number of fields in the result sets with similar data types.
 
The syntax for the TSQL UNION operator is:
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;

Return single field

The following is an example of the TSQL UNION operator that returns one field from multiple SELECT statements (and both fields have the same data type):
SELECT Customer_id
FROM Customers
UNION
SELECT Customer_id
FROM Order;
In this TSQL UNION operator example, if a customer_id appeared in both the suppliers and orders table, it would appear once in your result set. The TSQL UNION operator removes duplicates.

Using TSQL ORDER BY Clause with Union

The TSQL UNION operator can use the TSQL ORDER BY clause to order the results of the query.
For example:
SELECT customer_id, customer_name
FROM customer
UNION
SELECT customer_id, customer_name
FROM Order
ORDER BY 2;
Order will be always in the second query after UNION clause.

No comments:

Post a Comment