Working With Unions in SQL Server

Vipendra Verma

14y3.1k

Hi friends

I want to know how to work with unions in SQL Server.

Thank you.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Keyur Narkhi14y ago
Hi

Please follow below link

http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
MuthuMari M14y ago
Hi,

union

when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:
- The number and the order of the columns must be the same in all queries.
- The data types must be compatible.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values

UNION: This will remove duplicate values
UNION ALL: This will repeat values

thanks.
If this post is useful then mark it as "Accepted Answer"
Senthilkumar14y ago
Hi Vipendra,

In SQL Server you have the ability to combine multiple datasets into one comprehensive dataset by using the UNION or UNION ALL operators. There is a big difference in how these work as well as the final result set that is returned, but basically these commands join multiple datasets that have similar structures into one combined dataset.

Here is a brief description:

UNION - this command will allow you to join multiple datasets into one dataset and will remove any duplicates that exist. Basically it is performing a DISTINCT operation across all columns in the result set.
UNION ALL - this command again allows you to join multiple datasets into one dataset, but it does not remove any duplicate rows. Because this does not remove duplicate rows this process is faster, but if you don't want duplicate records you will need to use the UNION operator instead.
Rules to union data:

Each query must have the same number of columns
Each column must have compatible data types
Column names for the final result set are taken from the first query
ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set
GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set

Please go this url for more detail
http://www.mssqltips.com/sqlservertip/1387/joining-data-using-union-and-union-all-in-sql-server/
Satyapriya Nayak14y ago
Hi Vipendra,

SQL UNION query is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

The syntax is as follows:

[SQL Statement 1]
UNION
[SQL Statement 2]

Say we have the following two tables,

Table Store_Information
store_name     Sales     Date
Los Angeles     $1500     Jan-05-1999
San Diego     $250     Jan-07-1999
Los Angeles     $300     Jan-08-1999
Boston     $700     Jan-08-1999

Table Internet_Sales
Date     Sales
Jan-07-1999     $250
Jan-10-1999     $535
Jan-11-1999     $320
Jan-12-1999     $750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

Let's use the same example as the previous section to illustrate the difference. Assume that we have the following two tables,

Table Store_Information
store_name     Sales     Date
Los Angeles     $1500     Jan-05-1999
San Diego     $250     Jan-07-1999
Los Angeles     $300     Jan-08-1999
Boston     $700     Jan-08-1999

Table Internet_Sales
Date     Sales
Jan-07-1999     $250
Jan-10-1999     $535
Jan-11-1999     $320
Jan-12-1999     $750

and we want to find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

Result:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

Thanks
Kunal Vaishya14y ago
UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth

Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)
Mahak Gupta14y ago
Read this article:

http://www.w3schools.com/sql/sql_union.asp